Posted on 2010-07-24 16:22
斷點 閱讀(301)
評論(0) 編輯 收藏 所屬分類:
Oracle DBA
--觸發器
create table emp2_log
(
uname varchar2(20);
action varchar2(10);
atime date
);
create or replace trigger trig
after insert or delete or update on emp2 for each row
begin
if inserting then
insert into emp2_log values (USER,'insert',sysdate); --USER關鍵字，用戶。
elsif updating then
insert into emp2_log values (USER,'update',sysdate);
elsif deleting then
insert into emp2_log values (USER,'delete',sysdate);
end if;
end;
update emp2 set sal = sal*2 where deptno = 30;
select * from emp2_log;
drop trigger trig;
--直接執行時，出現違反完整約束條件，已找到子記錄。
update dept set deptno = 99 where deptno = 10;
--使用下面的，把子表一起更新。
create or replace trigger trig
after update on dept for each row
begin
update emp set deptno =:NEW.deptno where deptno =:OLD.deptno;
end;
update dept set deptno = 99 where deptno = 10;
select * from emp;
rollback;