pl/sql trigger
1 SQL> create or replace trigger my_trigger after update on scott.a for each row
2 2 begin
3 3 update scott.c set dep=:new.departno
4 4 where dep=:old.departno;
5 5 end;
6 6 /
7
8 --the occasion before running the trigger
9 SQL> select * from a
10 2 /
11
12 DEPARTNO DEPARTNAME NUM(PK)
13 ---------- --------------------
14 100 部门一 1
15 200 部门二 2
16 300 部门三 3
17
18 SQL> select * from c
19 2 /
20
21 C_NO C_NAME DEP
22 ---------- -------------------- ----------
23 1 1 100
24 2 2 200
25
26 --run the create trigger sentence
27 create or replace trigger my_trigger after update on scott.a for each row
28 begin
29 update scott.c set dep=:new.departno
30 where dep=:old.departno;
31 end;
32
33 Trigger created
34
35 --trigger the constrain and the event will be executed.
36 SQL> update a set departno=500 where departno=100;
37
38 --you can refer to the following result:
39 SQL> select * from a
40 2 /
41
42 DEPARTNO DEPARTNAME NUM
43 ---------- -------------------- ----------
44 500 部门一 1
45 200 部门二 2
46 300 部门三 3
47
48 SQL> select * from c
49 2 /
50
51 C_NO C_NAME DEP
52 ---------- -------------------- ----------
53 1 1 500
54 2 2 200
55
56 ---after insert，then we only read :new
57 create or replace trigger my_trigger after insert on a for each row
58 begin
59 dbms_output.put_line(:new.departno);
60 end;
61
62 ---after update，then we read both :old and :new
63 create or replace trigger my_trigger after update on a for each row
64 begin
65 dbms_output.put_line(:old.departno);
66 dbms_output.put_line(:new.departno);
67 end;
68
69 ---after delete，then we only read :old
70 create or replace trigger my_trigger after delete on a for each row
71 begin
72 dbms_output.put_line(:old.departno);
73 end;
74
75
76 --create a trigger and to log the date
77 --There are two tables: t_trigger_test01,t_trigger_log
78 drop table t_trigger_test01;
79 create table t_trigger_test01(
80 id integer primary key,
81 name varchar2(20)
82 );
83 drop table t_trigger_log;
84 create table t_trigger_log(
85 action_date date,
86 action varchar2(20)
87 );
88 create or replace trigger log_trigger
89 after insert or update or delete on t_trigger_test01
90 declare
91 v_action varchar2(20);
92 begin
93 if inserting then v_action:='insert';
94 elsif updating then v_action:='update';
95 elsif deleting then v_action:='delete';
96 else dbms_output.put_line('..');
97 end if;
98 insert into t_trigger_log values(sysdate, v_action);
99 end;
100
101 --create a rwo trigger to update a corresponding table when the colume value is changed
102 --two tables: t_class,t_student
103 drop table t_trigger_class;
104 create table t_trigger_class(
105 id number(10,0) primary key,
106 name varchar2(20)
107 );
108 drop table t_trigger_student;
109 create table t_trigger_student(
110 sno integer primary key,
111 name varchar2(20),
112 cno number(10,0)
113 );
114
115 create or replace trigger row_trigger after update on t_trigger_class for each row
116 begin
117 update t_trigger_student set cno=:new.id
118 where cno=:old.id;
119 end;
120 /
121
122 --create instead of tirgger
123 drop view v_trigger_stu_class;
124 create view v_trigger_stu_class
125 as
126 select c.id,c.name as cname,s.sno,s.name as sname,s.cno from scott.t_trigger_class c, scott.t_trigger_student s
127 where s.cno=c.id;
128
129
130 create or replace trigger instead_of_trigger
131 instead of update on v_trigger_stu_class
132 begin
133 update scott.t_trigger_class c set c.id=:new.id,c.name=:new.cname
134 where c.id=:old.id;
135 update scott.t_trigger_student s set s.sno=:new.sno,s.name=:new.sname,s.cno=:new.cno
136 where s.sno=:old.sno;
137 end;
138
2 2 begin
3 3 update scott.c set dep=:new.departno
4 4 where dep=:old.departno;
5 5 end;
6 6 /
7
8 --the occasion before running the trigger
9 SQL> select * from a
10 2 /
11
12 DEPARTNO DEPARTNAME NUM(PK)
13 ---------- --------------------
14 100 部门一 1
15 200 部门二 2
16 300 部门三 3
17
18 SQL> select * from c
19 2 /
20
21 C_NO C_NAME DEP
22 ---------- -------------------- ----------
23 1 1 100
24 2 2 200
25
26 --run the create trigger sentence
27 create or replace trigger my_trigger after update on scott.a for each row
28 begin
29 update scott.c set dep=:new.departno
30 where dep=:old.departno;
31 end;
32
33 Trigger created
34
35 --trigger the constrain and the event will be executed.
36 SQL> update a set departno=500 where departno=100;
37
38 --you can refer to the following result:
39 SQL> select * from a
40 2 /
41
42 DEPARTNO DEPARTNAME NUM
43 ---------- -------------------- ----------
44 500 部门一 1
45 200 部门二 2
46 300 部门三 3
47
48 SQL> select * from c
49 2 /
50
51 C_NO C_NAME DEP
52 ---------- -------------------- ----------
53 1 1 500
54 2 2 200
55
56 ---after insert，then we only read :new
57 create or replace trigger my_trigger after insert on a for each row
58 begin
59 dbms_output.put_line(:new.departno);
60 end;
61
62 ---after update，then we read both :old and :new
63 create or replace trigger my_trigger after update on a for each row
64 begin
65 dbms_output.put_line(:old.departno);
66 dbms_output.put_line(:new.departno);
67 end;
68
69 ---after delete，then we only read :old
70 create or replace trigger my_trigger after delete on a for each row
71 begin
72 dbms_output.put_line(:old.departno);
73 end;
74
75
76 --create a trigger and to log the date
77 --There are two tables: t_trigger_test01,t_trigger_log
78 drop table t_trigger_test01;
79 create table t_trigger_test01(
80 id integer primary key,
81 name varchar2(20)
82 );
83 drop table t_trigger_log;
84 create table t_trigger_log(
85 action_date date,
86 action varchar2(20)
87 );
88 create or replace trigger log_trigger
89 after insert or update or delete on t_trigger_test01
90 declare
91 v_action varchar2(20);
92 begin
93 if inserting then v_action:='insert';
94 elsif updating then v_action:='update';
95 elsif deleting then v_action:='delete';
96 else dbms_output.put_line('..');
97 end if;
98 insert into t_trigger_log values(sysdate, v_action);
99 end;
100
101 --create a rwo trigger to update a corresponding table when the colume value is changed
102 --two tables: t_class,t_student
103 drop table t_trigger_class;
104 create table t_trigger_class(
105 id number(10,0) primary key,
106 name varchar2(20)
107 );
108 drop table t_trigger_student;
109 create table t_trigger_student(
110 sno integer primary key,
111 name varchar2(20),
112 cno number(10,0)
113 );
114
115 create or replace trigger row_trigger after update on t_trigger_class for each row
116 begin
117 update t_trigger_student set cno=:new.id
118 where cno=:old.id;
119 end;
120 /
121
122 --create instead of tirgger
123 drop view v_trigger_stu_class;
124 create view v_trigger_stu_class
125 as
126 select c.id,c.name as cname,s.sno,s.name as sname,s.cno from scott.t_trigger_class c, scott.t_trigger_student s
127 where s.cno=c.id;
128
129
130 create or replace trigger instead_of_trigger
131 instead of update on v_trigger_stu_class
132 begin
133 update scott.t_trigger_class c set c.id=:new.id,c.name=:new.cname
134 where c.id=:old.id;
135 update scott.t_trigger_student s set s.sno=:new.sno,s.name=:new.sname,s.cno=:new.cno
136 where s.sno=:old.sno;
137 end;
138
