17,090
社区成员
发帖
与我相关
我的任务
分享
SQL> DROP TABLE test;
Table dropped
SQL>
SQL> CREATE TABLE test
2 (a NUMBER(2));
Table created
SQL>
SQL> CREATE OR REPLACE TRIGGER TR_TEST
2 AFTER INSERT ON TEST
3 FOR EACH ROW
4 DECLARE
5 v_count NUMBER(2);
6 pragma autonomous_transaction;
7 BEGIN
8
9 SELECT COUNT(*) INTO v_count FROM test WHERE a=:new.a;
10 IF v_count>0 THEN
11 UPDATE test SET a=:new.a+1 WHERE a=:new.a;
12 END IF;
13 COMMIT;
14 END TR_TEST;
15 /
Trigger created
SQL> insert into test values(1);
1 row inserted
SQL> select * from test;
A
---
1
SQL> insert into test values(1);
1 row inserted
SQL> select * from test;
A
---
1
1
SQL> commit;
Commit complete
SQL> insert into test values(1);
1 row inserted
SQL> select * from test;
A
---
2
2
1
SQL> commit;
Commit complete
SQL> select * from test;
A
---
2
2
1
SQL>
create or replace trigger tri_tb before insert on mrzbxx for each row
declare
pragma autonomous_transaction;--表内自更新要加
begin
update mrzbxx set obj3=:new.obj1+:new.obj2 where :old.obj1=:new.obj1 and :old.obj2=:new.obj2;
end;
update mrzbxx set obj3=:new.obj1||:new.obj2 where :old.obj1=:new.obj1 and :old.obj2=:new.obj2;
create or replace trigger tri_tb before insert on mrzbxx for each row
declare
pragma autonomous_transaction;
begin
update mrzbxx set obj3=:new.obj1+:new.obj2 where :old.obj1=:new.obj1 and :old.obj2=:new.obj2;
end;