Posted on 2010-07-24 16:22
斷點 閱讀(298)
評論(0) 編輯 收藏 所屬分類:
Oracle DBA
--觸發(fā)器
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關(guān)鍵字，用戶。
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;
--直接執(zhí)行時，出現(xiàn)違反完整約束條件，已找到子記錄。
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;