PL/SQL 11g R2 —— 触发器

一、语句触发器

语法：
CREATE [OR REPLACE] TRIGGER trigger_name
 {BEFORE | AFTER } event1 [OR event2 OR event3]
ON table_name
PL/SQL block;

21-1:禁止在周六、周日改变EMP表的数据
    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') THEN
          raise_application_error(-20001,
           '不能在休息日改变雇员信息');
      END IF;
    END;
    /
    
21-2:
    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;
    /
    
INSERTING：当触发事件是INSERT语句时，返回TRUE，否则返回FALSE。
UPDATING：当触发事件是UPDATE语句时，返回TRUE，否则返回FALSE。
DELETING：当触发事件是DELETE语句时，返回TRUE，否则返回FALSE。    
21-3:
    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') 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;
    /
    

二、行触发器

语法：
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;
--REFERENCING子句用于指定引用新、旧数据的方式，
--默认情况下使用old操作符引用旧数据，使用new操作符引用新数据。

21-4:确保雇员工资不能低于原工资。
    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;
    /
    
21-5:
    CREATE TABLE audit_emp_change (
      name VARCHAR2(10),oldsal NUMBER(6,2),
      newsal NUMBER(6,2),time DATE);
    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;
    /
    
21-6:限制触发器
    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;
    /
    

三、组合触发器（11G新特性）

21-7:
CREATE OR REPLACE TRIGGER tr_update_sal
FOR UPDATE OF sal ON emp COMPOUND TRIGGER
  msg1 VARCHAR2(50):='工资必须在1000到5000之间!';
  msg2 VARCHAR2(50):='不能在休息日更新!';
BEFORE STATEMENT IS
BEGIN
  IF to_char(sysdate,'DY','nls_date_language=AMERICAN')
     IN ('SAT','SUN') THEN
     raise_application_error(-20000,msg2);
  END IF;
END BEFORE STATEMENT;
AFTER EACH ROW IS
BEGIN
  IF :new.sal NOT BETWEEN 1000 AND 5000 THEN
     raise_application_error(-20001,msg1);
  END IF;
END AFTER EACH ROW;
END;
/

四、触发器示例

21-8:限制修饰数据的时间。
    CREATE OR REPLACE TRIGGER tr_emp_time
    BEFORE INSERT OR UPDATE OR DELETE 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=3200 WHERE empno=7788;
    
21-9:审计删除
    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 empno=7788;
    SELECT * FROM audit_delete_emp;
    
21-10:实现数据完整性
    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=sal*1.25 WHERE empno=7788;
    
21-11:实现参照完整性
    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;
    /
    UPDATE dept SET deptno=50 WHERE deptno=10;
    SELECT ename FROM emp WHERE deptno=50;
    

五、INSTEAD OF触发器

当复杂视图子查询包含有集合操作符、分组函数、distinct关键字或者连接查询，那么将禁止在该视图上执行DML操作。
为了在这些复杂视图上执行DML操作，需要建立INSTEAD OF触发器。
INSTEAD OF触发器只适用于视图。
INSTEAD OF触发器不能指定BEFORE和AFTER选项。
不能在具有WITH CHECK OPTION选项的视图上建立INSTEAD OF触发器。
INSTEAD OF触发器必须包含FOR EACH ROW选项。
21-12:
    CREATE OR REPLACE VIEW dept_emp AS
      SELECT a.deptno,a.dname,b.empno,b.ename FROM dept a,emp b
      WHERE a.deptno=b.deptno;
    CREATE OR REPLACE TRIGGER tr_instead_of_dept_emp
    INSTEAD OF INSERT ON dept_emp FOR EACH ROW
    DECLARE
      v_temp INT;
    BEGIN
      SELECT count(*) INTO v_temp FROM dept WHERE deptno=:new.deptno;
      IF v_temp=0 THEN
         INSERT INTO dept (deptno,dname) VALUES(:new.deptno,:new.dname);
       END IF;
      SELECT count(*) INTO v_temp FROM emp WHERE empno=:new.empno;
      IF v_temp=0 THEN
         INSERT INTO emp (empno,ename,deptno) VALUES(:new.empno,:new.ename,:new.deptno);
      END IF;
    END;
    /
    INSERT INTO dept_emp VALUES(50,'ADMIN',1223,'MARY');
    SELECT dname FROM dept WHERE deptno=50;
    SELECT ename FROM emp WHERE empno=1223;
    

六、事件触发器

21-13:数据库启动之后触发。
    conn sys/oracle as sysdba
    create table event_table(event varchar2(30),time date);
    CREATE OR REPLACE TRIGGER tr_startup
    AFTER STARTUP ON DATABASE
    BEGIN
      INSERT INTO event_table VALUES(ora_sysevent,SYSDATE);
    END;
    /
    SHUTDOWN
    STARTUP
    SELECT * FROM event_table;
    
21-14:用户登录之后触发。
    conn sys/oracle as sysdba
    CREATE TABLE aud_logon_tab(
       username VARCHAR2(20),time DATE,addr VARCHAR2(20));
    CREATE OR REPLACE TRIGGER tr_logon
    AFTER LOGON ON DATABASE
    BEGIN
      INSERT INTO aud_logon_tab VALUES(ora_login_user,SYSDATE,ora_client_ip_address);
    END;
    /
    conn scott/tiger@test
    SELECT * FROM sys.aud_logon_tab;
    
21-15:DDL触发器
    conn sys/oracle as sysdba
    CREATE TABLE aud_ddl_tab(
       event VARCHAR2(20),username VARCHAR2(10),
       owner VARCHAR2(10),objname VARCHAR2(20),
      objtype VARCHAR2(10),time DATE);
    CREATE OR REPLACE TRIGGER tr_ddl
    AFTER DDL ON scott.schema
    BEGIN
      INSERT INTO aud_ddl_tab VALUES(
        ora_sysevent,ora_login_user,ora_dict_obj_owner,
        ora_dict_obj_name,ora_dict_obj_type,SYSDATE);
    END;
    /
    conn scott/tiger@test
    CREATE TABLE temp(cola INT);
    DROP TABLE temp;
    SELECT username,event,objtype,objname FROM sys.aud_ddl_tab;
    

七、维护触发器    

21-16:
    set long 800
    set pagesize 400
    SELECT trigger_body FROM user_triggers WHERE trigger_name='TR_UPD_EMP';
21-17:
    ALTER TRIGGER tr_upd_emp DISABLE;
21-18:
    ALTER TRIGGER tr_upd_emp ENABLE;
21-19:
    ALTER TRIGGER tr_upd_emp COMPILE;
21-20:
    DROP TRIGGER tr_check_sal;