我正在尝试创建一个触发器,它自动计算col1 + col2,并在数据插入后将总和插入col3。由于某种原因,有一个错误,有人知道如何做到这一点吗?
CREATE OR REPLACE TRIGGER test_tr
AFTER INSERT ON test
FOR EACH ROW
DECLARE
test_one NUMBER(10);
test_two NUMBER(10);
BEGIN
SELECT random
INTO test_one
FROM test
WHERE id = id;
SELECT random2
INTO test_two
FROM test
WHERE id = id;
INSERT INTO test(test3) values ((test_one+test_two));
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
不要从同一个表中选择,因为触发器不会看到它;桌子在变异。使用pseudorecords。
SQL> create table test_one (random number, random2 number, test3 number);
Table created.
SQL> create or replace trigger test_tr
2 before insert on test_one
3 for each row
4 begin
5 :new.test3 := :new.random + :new.random2;
6 end;
7 /
Trigger created.
SQL> insert into test_One (random, random2)
2 select 1, 2 from dual union all
3 select 5, 3 from dual;
2 rows created.
SQL> select * from test_One;
RANDOM RANDOM2 TEST3
---------- ---------- ----------
1 2 3
5 3 8
SQL>
另一方面,为什么要用触发器呢?创建虚拟列:
SQL> create table test_one
2 (random number,
3 random2 number,
4 test3 number as (random + random2) virtual
5 );
Table created.
SQL> insert into test_One (random, random2) values (3, 9);
1 row created.
SQL> select * from test_one;
RANDOM RANDOM2 TEST3
---------- ---------- ----------
3 9 12
SQL>
或者,不创建任何,并在需要时计算random + random2
。
不要使用触发器,从Oracle 11gR1开始,您可以使用虚拟列:
ALTER TABLE test ADD test3 NUMBER GENERATED ALWAYS AS (test1 + test2);
或者,当你想显示然后在SELECT
子句中计算test3
值:
SELECT test1, test2, test1 + test2 AS test3 FROM test;
db<此处小提琴>此处小提琴>