SQL> desc t1
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
A NUMBER
SQL> desc t2
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
A NUMBER
SQL> create or replace trigger test
2 after insert or update or delete on t1 for each row
3 declare
4 integrity_error exception;
5 errno integer;
6 errmsg char(200);
7 dummy integer;
8 found boolean;
9 begin
10 if inserting then
11 insert into t2 values(:NEW.id,:NEW.a);
12 elsif updating then
13 update t2 set id=:NEW.id,a=:NEW.a where id=:OLD.id;
14 elsif deleting then
15 delete from t2 where id=:old.id;
16 end if;
17 exception
18 when integrity_error then
19 raise_application_error(errno,errmsg);
20 end;
21 /
Trigger created.
SQL> insert into t1 values(100,100);
SQL> insert into t1 values(200,200);
SQL> insert into t1 values(300,300);
SQL> commit;
SQL> select * from t1 order by id;
ID A
---------- ----------
100 100
200 200
300 300
SQL> select * from t2 order by id;
ID A
---------- ----------
100 100
200 200
300 300
SQL> update t1 set id=1 where a=100;
1 row updated.
SQL> select * from t1;
ID A
---------- ----------
300 300
1 100
200 200
SQL> select * from t2;
ID A
---------- ----------
300 300
1 100
200 200
SQL> delete t1 where id=1;
1 row deleted.
SQL> select * from t1;
ID A
---------- ----------
300 300
200 200
SQL> select * from t2;
ID A
---------- ----------
300 300
200 200
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
A NUMBER
SQL> desc t2
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
A NUMBER
SQL> create or replace trigger test
2 after insert or update or delete on t1 for each row
3 declare
4 integrity_error exception;
5 errno integer;
6 errmsg char(200);
7 dummy integer;
8 found boolean;
9 begin
10 if inserting then
11 insert into t2 values(:NEW.id,:NEW.a);
12 elsif updating then
13 update t2 set id=:NEW.id,a=:NEW.a where id=:OLD.id;
14 elsif deleting then
15 delete from t2 where id=:old.id;
16 end if;
17 exception
18 when integrity_error then
19 raise_application_error(errno,errmsg);
20 end;
21 /
Trigger created.
SQL> insert into t1 values(100,100);
SQL> insert into t1 values(200,200);
SQL> insert into t1 values(300,300);
SQL> commit;
SQL> select * from t1 order by id;
ID A
---------- ----------
100 100
200 200
300 300
SQL> select * from t2 order by id;
ID A
---------- ----------
100 100
200 200
300 300
SQL> update t1 set id=1 where a=100;
1 row updated.
SQL> select * from t1;
ID A
---------- ----------
300 300
1 100
200 200
SQL> select * from t2;
ID A
---------- ----------
300 300
1 100
200 200
SQL> delete t1 where id=1;
1 row deleted.
SQL> select * from t1;
ID A
---------- ----------
300 300
200 200
SQL> select * from t2;
ID A
---------- ----------
300 300
200 200
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29785807/viewspace-1304829/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29785807/viewspace-1304829/