oracle触发器

--------------------------------------触发器---------------------------- 触发器是指存放在数据库中，并且被隐含执行的存储过程。包括语句触发器和行触发器 --DML触发器 -语句触发器 create or replace trigger tiegger_name {before|after} event1[or event2 or event3] on table_name; pl/sql block; 1:建立before语句触发器 create or replace trigger tr_sec_emp before insert or update or delete on emp begin if to_char(sysdate,'DY','nls_date_language=american') in ('SAT','SUN','TUE') THEN RAISE_APPLICATION_ERROR(-20001,'不能在休息日改变雇员信息'); end if; end; 执行触发器 UPDATE EMP SET SAL=SAL*1.1 WHERE DEPTNO=30; 2：建立after语句触发器 create table aud_upd_table (host varchar2(30),statement varchar2(100),exectime DATE); create or replace trigger tr_upd_emp after update on emp declare sql_txt ora_name_list_t; v_stmt varchar2(100); n binary_integer; begin n :=ora_sql_txt(sql_txt); for i in 1..n loop v_stmt := v_stmt||sql_txt(i); end loop; insert into aud_upd_table values (sys_context('userenv','host'),v_stmt,SYSDATE); END; drop trigger tr_upd_emp UPDATE EMP SET SAL=2000 WHERE EMPNO=1113; SELECT statement,exectime from aud_upd_table; 3:使用条件谓语 inserting、updating、deleting 当触发事件时返回true，否则返回false create or replace trigger tr_sec_emp before insert or update or delete on emp begin if to_char(sysdate,'DY','nls_date_language=american') in('SUN','SAT','TUE') then case when inserting then RAISE_APPLICATION_ERROR(-20001,'不能在休息日insert'); when updating then RAISE_APPLICATION_ERROR(-20002,'不能在休息日update'); when deleting then RAISE_APPLICATION_ERROR(-20003,'不能在休息日delete'); end case; end if; end; select * from emp delete from emp where empno=22222 -行触发器 create or replace trigger trigger_name {before|after} event1[or event2 or event3] on table_name [referencing old as old | new as new] for each row [WHEN condition] pl/sql block; 1:建立before行触发器 create or replace trigger tr_emp_sal before update of sal on emp for each row begin if :new.sal < :old.sal then RAISE_APPLICATION_ERROR(-20010,'工资只涨不降'); end if; end; select * from emp; update emp set sal=100 where empno=1113 2:建立after行触发器 drop table audit_emp_change create table audit_emp_change (name varchar2(10),oldsal int,newsal int,time DATE); SELECT * from audit_emp_change create or replace trigger tr_sal_change after update of sal on emp for each row declare v_temp int; begin select count(*) into v_temp from audit_emp_change where name=:old.ename; if v_temp =0 then insert into audit_emp_change values(:old.ename,:old.sal,:new.sal,SYSDATE); else update audit_emp_change set oldsal=:old.sal,newsal = :new.sal,time = SYSDATE WHERE name=:old.ename; end if; end; update emp set sal = sal*1.1 where deptno=60 select * from emp select * from audit_emp_change 3:限制触发器 在特定条件下执行触发器 create or replace trigger tr_sal_change after update of sal on emp for each row when (old.job = 'salesman') declare v_temp int; begin select count(*) into v_temp from audit_emp_change where name=:old.ename; if v_temp =0 then insert into audit_emp_change values(:old.ename,:old.sal,:new.sal,SYSDATE); else update audit_emp_change set oldsal=:old.sal,newsal = :new.sal,time = SYSDATE WHERE name=:old.ename; end if; end; -dml触发器开发示例 1：控制数据安全 在特定的时间才能修改 create or replace trigger tr_emp_time before insert or update on emp begin if to_char(SYSDATE,'HH24') NOT BETWEEN '9' AND '17' THEN RAISE_APPLICATION_ERROR(-20101,'非工作时间'); end if; end; update emp set sal=10000 where empno=60 2:实现数据审计 审计删除时间和删除的雇员 create table audit_delete_emp (name varchar2(10),time date); create or replace trigger tr_delete_emp after delete on emp for each row begin insert into audit_delete_emp values(:old.ename,sysdate); end; delete from emp where deptno=60; select * from audit_delete_emp 3：实现数据完整性 确保一些商业逻辑活企业规则 create or replace trigger tr_check_sal before update of sal on emp for each row when (new.sal < old.sal or new.sal > 1.2*old.sal) begin raise_application_error(-20931,'工资只涨不降，并且升幅不能超过20%'); end; update emp set sal = 100 where empno=1113 4：实现参照完整性 是指两张表之间具有主从关系 create or replace trigger tr_update_cascade after update of deptno on dept for each row begin update emp set deptno = :new.deptno where deptno = :old.deptno; end; --instead of 触发器 --事件触发器 --维护触发器