--创建触发器(行级触发器)6 W6 A3 J( H$ x" Z6 l
create or replace trigger tri4_update_emp_bak" l* K# D) W* a9 w) f: B3 x
after update of sal, V# D9 F% ]0 Q/ r$ x" ]- P8 J' y
on emp_bak$ s$ D: C' W4 n; O+ i& Q
for each row --每更新一行 就触发一次8 X) D8 y- A! Q4 Z0 r
begin, y9 P- g/ q9 U' |! ^" d' q
--oracle 里面 对触发器 也提供了特殊的对象 :NEW :OLD 来访问 更新前后的数据
* }. Z! x2 h0 X- V z dbms_output.put_line('更新后' || :NEW.sal);
3 s5 r9 M0 K: T* F& [( h6 ? dbms_output.put_line('更新前' || :OLD.sal);
" i( s: r2 ?2 Q end;
( K+ D2 R, L- f0 A create table userinfo. N. b4 w/ V3 O8 t5 \
(
2 `. p; P/ o4 ~$ j" |4 L userid number(4) PRimary key,
+ P/ n+ q2 ]+ j* P# W- P# t: _ username varchar2(20)
2 M- p1 _, k, ` )2 N8 ^0 S# \8 q* l: n5 n
create table addrinfo
# V: z9 U8 _. G* c, a$ M& z (( D+ \) N) |( w1 {
addrid number(4) primary key,3 W7 X* Y t& \, n! T3 ~
addname varchar2(20),/ a) e; l% a) Q ~$ @! S5 k
userid number(4) references userinfo(userid)- d" C" s, H5 W8 A8 T' H
)
; Q, L$ \3 C$ e% v8 j# g insert into userinfo values (1,'李四')
& ]2 C; f8 X2 L( J8 i9 o: O5 s insert into addrinfo values (1,'湖北武汉',1)
0 ?% a5 i0 J# h* g1 q' w1 B9 S select * from addrinfo
8 D/ o; B9 l0 Z7 D$ v delete from userinfo where userid =1+ c i3 a+ G3 \; D
--级联删除# b9 R! g* r, \* L C
create or replace trigger tri_userinfo_delete
% f) k/ b8 r% ^/ H- N before delete1 g: f7 G) p: r& F2 {8 L
on userinfo
5 _0 F% ?: y) _2 h7 n- U+ K for each row
7 N$ y1 x3 y2 `# p begin# C/ ]; {( G% F; w
delete from addrinfo where userid = :OLD.userid;6 E; J; I0 D3 ?3 m7 d
-- insert into) o0 J/ `- y* q4 \5 Z
end;
! E8 W1 k; `" f7 V: x2 t) N- R3 y) A create table dept_bak as select * from dept
: K6 F* h' x" I0 L/ q! L$ [8 V create or replace view myview% f- _2 K n; m' J5 r/ l; M6 K$ Y
as9 g3 J% o1 X8 @4 z8 N
select a.empno,a.ename,a.sal,b.dname% a k) I, V! u4 y. X
from emp a inner join dept b
$ w) M+ Z% A- C( l# e6 `- f. T on a.deptno = b.deptno
5 n4 Y+ U& v8 W2 F7 c4 J+ w select * from myview |