ORACLE PL/SQL编程之八（二）：把触发器说透

首页 > 代码库 > ORACLE PL/SQL编程之八（二）：把触发器说透

ORACLE PL/SQL编程之八（二）：把触发器说透

2024-07-05 22:06:15 219人阅读

例2：创建DML语句行级触发器。当对emp表执行INSERT, UPDATE, DELETE 操作时，它自动更新dept_summary 表中的数据。由于在PL/SQL块中不能直接调用DDL语句，所以，利用ORACLE内置包DBMS_UTILITY中的EXEC_DDL_STATEMENT过程，由它执行DDL语句创建触发器。

BEGIN
  DBMS_OUTPUT.PUT_LINE(‘插入前‘);
  Disp_dept_summary();
  DBMS_UTILITY.EXEC_DDL_STATEMENT(
    ‘CREATE OR REPLACE TRIGGER trig2_update
      AFTER UPDATE OF sal ON emp
      REFERENCING OLD AS old_emp NEW AS new_emp
      FOR EACH ROW
      WHEN (old_emp.sal != new_emp.sal)
    BEGIN
      DBMS_OUTPUT.PUT_LINE(‘‘正在执行trig2_update 触发器…‘‘);
      DBMS_OUTPUT.PUT_LINE(‘‘sal 旧值：‘‘|| :old_emp.sal);
      DBMS_OUTPUT.PUT_LINE(‘‘sal 新值：‘‘|| :new_emp.sal);
      UPDATE dept_summary
        SET sal_sum=sal_sum + :new_emp.sal - :old_emp.sal
        WHERE deptno = :new_emp.deptno;
    END;‘
  );

  DBMS_UTILITY.EXEC_DDL_STATEMENT(
    ‘CREATE OR REPLACE TRIGGER trig2_insert
      AFTER INSERT ON emp
      REFERENCING NEW AS new_emp
      FOR EACH ROW
    DECLARE
      I NUMBER;
    BEGIN
      DBMS_OUTPUT.PUT_LINE(‘‘正在执行trig2_insert 触发器…‘‘);
      SELECT COUNT(*) INTO I
      FROM dept_summary WHERE deptno = :new_emp.deptno;
      IF I > 0 THEN
        UPDATE dept_summary
        SET sal_sum=sal_sum+:new_emp.sal,
        Emp_count=emp_count+1
        WHERE deptno = :new_emp.deptno;
      ELSE
        INSERT INTO dept_summary
        VALUES (:new_emp.deptno, :new_emp.sal, 1);
      END IF;
    END;‘
  );

  DBMS_UTILITY.EXEC_DDL_STATEMENT(
    ‘CREATE OR REPLACE TRIGGER trig2_delete
      AFTER DELETE ON emp
      REFERENCING OLD AS old_emp
      FOR EACH ROW
    DECLARE
      I NUMBER;
    BEGIN
      DBMS_OUTPUT.PUT_LINE(‘‘正在执行trig2_delete 触发器…‘‘);
      SELECT emp_count INTO I
      FROM dept_summary WHERE deptno = :old_emp.deptno;
      IF I >1 THEN
        UPDATE dept_summary
        SET sal_sum=sal_sum - :old_emp.sal,
        Emp_count=emp_count - 1
        WHERE deptno = :old_emp.deptno;
      ELSE
        DELETE FROM dept_summary WHERE deptno = :old_emp.deptno;
      END IF;
    END;‘
  );

  INSERT INTO dept(deptno, dname, loc)
    VALUES(90, ‘demo_dept‘, ‘none_loc‘);
  INSERT INTO emp(ename, deptno, empno, sal)
    VALUES(USER, 90, 9999, 3000);
  INSERT INTO emp(ename, deptno, empno, sal)
    VALUES(USER, 90, 9998, 2000);
  DBMS_OUTPUT.PUT_LINE(‘插入后‘);
  Disp_dept_summary();

  UPDATE emp SET sal = sal*1.1 WHERE deptno=90;
  DBMS_OUTPUT.PUT_LINE(‘修改后‘);
  Disp_dept_summary();

  DELETE FROM emp WHERE deptno=90;
  DELETE FROM dept WHERE deptno=90;
  DBMS_OUTPUT.PUT_LINE(‘删除后‘);
  Disp_dept_summary();

  DBMS_UTILITY.EXEC_DDL_STATEMENT(‘DROP TRIGGER trig2_update‘);
  DBMS_UTILITY.EXEC_DDL_STATEMENT(‘DROP TRIGGER trig2_insert‘);
  DBMS_UTILITY.EXEC_DDL_STATEMENT(‘DROP TRIGGER trig2_delete‘);
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||‘---‘||SQLERRM);
END;

例3：利用ORACLE提供的条件谓词INSERTING、UPDATING和DELETING创建与例2具有相同功能的触发器。

BEGIN
    DBMS_OUTPUT.PUT_LINE(‘插入前‘);
    Disp_dept_summary();
    DBMS_UTILITY.EXEC_DDL_STATEMENT(
        ‘CREATE OR REPLACE TRIGGER trig2
            AFTER INSERT OR DELETE OR UPDATE OF sal
ON emp
            REFERENCING OLD AS old_emp NEW AS new_emp
            FOR EACH ROW
        DECLARE
            I NUMBER;
        BEGIN
            IF UPDATING AND :old_emp.sal != :new_emp.sal THEN
            DBMS_OUTPUT.PUT_LINE(‘‘正在执行trig2 触发器…‘‘);
                DBMS_OUTPUT.PUT_LINE(‘‘sal 旧值：‘‘|| :old_emp.sal);
                DBMS_OUTPUT.PUT_LINE(‘‘sal 新值：‘‘|| :new_emp.sal);
                UPDATE dept_summary
                    SET sal_sum=sal_sum + :new_emp.sal - :old_emp.sal
                WHERE deptno = :new_emp.deptno;
            ELSIF INSERTING THEN
                DBMS_OUTPUT.PUT_LINE(‘‘正在执行trig2触发器…‘‘);
                SELECT COUNT(*) INTO I
        FROM dept_summary
        WHERE deptno = :new_emp.deptno;
                IF I > 0 THEN
                    UPDATE dept_summary
          SET sal_sum=sal_sum+:new_emp.sal,
              Emp_count=emp_count+1
          WHERE deptno = :new_emp.deptno;
            ELSE
          INSERT INTO dept_summary
            VALUES (:new_emp.deptno, :new_emp.sal, 1);
        END IF;
      ELSE
        DBMS_OUTPUT.PUT_LINE(‘‘正在执行trig2触发器…‘‘);
        SELECT emp_count INTO I
        FROM dept_summary WHERE deptno = :old_emp.deptno;
      IF I > 1 THEN
        UPDATE dept_summary
        SET sal_sum=sal_sum - :old_emp.sal,
        Emp_count=emp_count - 1
        WHERE deptno = :old_emp.deptno;
      ELSE
          DELETE FROM dept_summary
          WHERE deptno = :old_emp.deptno;
      END IF;
    END IF;
    END;‘
  );

  INSERT INTO dept(deptno, dname, loc)
    VALUES(90, ‘demo_dept‘, ‘none_loc‘);
  INSERT INTO emp(ename, deptno, empno, sal)
    VALUES(USER, 90, 9999, 3000);
  INSERT INTO emp(ename, deptno, empno, sal)
    VALUES(USER, 90, 9998, 2000);
  DBMS_OUTPUT.PUT_LINE(‘插入后‘);
  Disp_dept_summary();

  UPDATE emp SET sal = sal*1.1 WHERE deptno=90;
  DBMS_OUTPUT.PUT_LINE(‘修改后‘);
  Disp_dept_summary();

  DELETE FROM emp WHERE deptno=90;
  DELETE FROM dept WHERE deptno=90;
  DBMS_OUTPUT.PUT_LINE(‘删除后‘);
  Disp_dept_summary();

  DBMS_UTILITY.EXEC_DDL_STATEMENT(‘DROP TRIGGER trig2‘);
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||‘---‘||SQLERRM);
END;

例4：创建INSTEAD OF 触发器。首先创建一个视图myview,由于该视图是复合查询所产生的视图，所以不能执行DML语句。根据用户对视图所插入的数据判断需要将数据插入到哪个视图基表中，然后对该基表执行插入操作。

DECLARE
    No NUMBER;
    Name VARCHAR2(20);
BEGIN
    DBMS_UTILITY.EXEC_DDL_STATEMENT(‘
        CREATE OR REPLACE VIEW myview AS
            SELECT empno, ename, ‘‘E‘‘ type FROM emp
            UNION
            SELECT dept.deptno, dname, ‘‘D‘‘ FROM dept
    ‘);
    -- 创建INSTEAD OF 触发器trigger3;
    DBMS_UTILITY.EXEC_DDL_STATEMENT(‘
        CREATE OR REPLACE TRIGGER trig3
            INSTEAD OF INSERT ON myview
            REFERENCING NEW n
            FOR EACH ROW
        DECLARE
            Rows INTEGER;
        BEGIN
            DBMS_OUTPUT.PUT_LINE(‘‘正在执行trig3触发器…‘‘);
            IF :n.type = ‘‘D‘‘ THEN
                SELECT COUNT(*) INTO rows
                    FROM dept WHERE deptno = :n.empno;
                IF rows = 0 THEN
                    DBMS_OUTPUT.PUT_LINE(‘‘向dept表中插入数据…‘‘);
                    INSERT INTO dept(deptno, dname, loc)
                        VALUES (:n.empno, :n.ename, ‘‘none’’);
                ELSE
                    DBMS_OUTPUT.PUT_LINE(‘‘编号为‘‘|| :n.empno||
                     ‘‘的部门已存在，插入操作失败！‘‘);
                 END IF;
            ELSE
                SELECT COUNT(*) INTO rows
                    FROM emp WHERE empno = :n.empno;
                IF rows = 0 THEN
                    DBMS_OUTPUT.PUT_LINE(‘’向emp表中插入数据…’’);
                    INSERT INTO emp(empno, ename)
                        VALUES(:n.empno, :n.ename);
                ELSE
                    DBMS_OUTPUT.PUT_LINE(‘‘编号为‘‘|| :n.empno||
                      ‘‘的人员已存在，插入操作失败!‘‘);
                END IF;
            END IF;
        END;
    ‘);

    INSERT INTO myview VALUES (70, ‘demo‘, ‘D‘);
    INSERT INTO myview VALUES (9999, USER, ‘E‘);
    SELECT deptno, dname INTO no, name FROM dept WHERE deptno=70;
    DBMS_OUTPUT.PUT_LINE(‘员工编号：‘||TO_CHAR(no)||‘姓名：‘||name);
    SELECT empno, ename INTO no, name FROM emp WHERE empno=9999;
    DBMS_OUTPUT.PUT_LINE(‘部门编号：‘||TO_CHAR(no)||‘姓名：‘||name);
  DELETE FROM emp WHERE empno=9999;
  DELETE FROM dept WHERE deptno=70;
    DBMS_UTILITY.EXEC_DDL_STATEMENT(‘DROP TRIGGER trig3‘);
END;

例5：利用ORACLE事件属性函数，创建一个系统事件触发器。首先创建一个事件日志表eventlog，由它存储用户在当前数据库中所创建的数据库对象，以及用户的登陆和注销、数据库的启动和关闭等事件，之后创建trig4_ddl、trig4_before和trig4_after触发器，它们调用事件属性函数将各个事件记录到eventlog数据表中。

BEGIN
    -- 创建用于记录事件日志的数据表
    DBMS_UTILITY.EXEC_DDL_STATEMENT(‘
        CREATE TABLE eventlog(
            Eventname VARCHAR2(20) NOT NULL,
            Eventdate date default sysdate,
            Inst_num NUMBER NULL,
            Db_name VARCHAR2(50) NULL,
            Srv_error NUMBER NULL,
            Username VARCHAR2(30) NULL,
            Obj_type VARCHAR2(20) NULL,
            Obj_name VARCHAR2(30) NULL,
            Obj_owner VARCHAR2(30) NULL
        )
    ‘);

    -- 创建DDL触发器trig4_ddl
    DBMS_UTILITY.EXEC_DDL_STATEMENT(‘
        CREATE OR REPLACE TRIGGER trig4_ddl
            AFTER CREATE OR ALTER OR DROP
ON DATABASE
        DECLARE
            Event VARCHAR2(20);
            Typ VARCHAR2(20);
            Name VARCHAR2(30);
            Owner VARCHAR2(30);
        BEGIN
            -- 读取DDL事件属性
            Event := SYSEVENT;
            Typ := DICTIONARY_OBJ_TYPE;
            Name := DICTIONARY_OBJ_NAME;
            Owner := DICTIONARY_OBJ_OWNER;
            --将事件属性插入到事件日志表中
            INSERT INTO scott.eventlog(eventname, obj_type, obj_name, obj_owner)
                VALUES(event, typ, name, owner);
        END;
    ‘);

    -- 创建LOGON、STARTUP和SERVERERROR 事件触发器
    DBMS_UTILITY.EXEC_DDL_STATEMENT(‘
        CREATE OR REPLACE TRIGGER trig4_after
            AFTER LOGON OR STARTUP OR SERVERERROR
      ON DATABASE
        DECLARE
            Event VARCHAR2(20);
            Instance NUMBER;
            Err_num NUMBER;
            Dbname VARCHAR2(50);
            User VARCHAR2(30);
        BEGIN
            Event := SYSEVENT;
            IF event = ‘‘LOGON‘‘ THEN
                User := LOGIN_USER;
                INSERT INTO eventlog(eventname, username)
                    VALUES(event, user);
            ELSIF event = ‘‘SERVERERROR‘‘ THEN
                Err_num := SERVER_ERROR(1);
                INSERT INTO eventlog(eventname, srv_error)
                    VALUES(event, err_num);
            ELSE
                Instance := INSTANCE_NUM;
                Dbname := DATABASE_NAME;
                INSERT INTO eventlog(eventname, inst_num, db_name)
                    VALUES(event, instance, dbname);
      END IF;
    END;
  ‘);

  -- 创建LOGOFF和SHUTDOWN 事件触发器
  DBMS_UTILITY.EXEC_DDL_STATEMENT(‘
    CREATE OR REPLACE TRIGGER trig4_before
      BEFORE LOGOFF OR SHUTDOWN
      ON DATABASE
    DECLARE
      Event VARCHAR2(20);
      Instance NUMBER;
      Dbname VARCHAR2(50);
      User VARCHAR2(30);
    BEGIN
      Event := SYSEVENT;
      IF event = ‘‘LOGOFF‘‘ THEN
        User := LOGIN_USER;
        INSERT INTO eventlog(eventname, username)
          VALUES(event, user);
      ELSE
        Instance := INSTANCE_NUM;
        Dbname := DATABASE_NAME;
        INSERT INTO eventlog(eventname, inst_num, db_name)
          VALUES(event, instance, dbname);
      END IF;
    END;
  ‘);
END;

CREATE TABLE mydata(mydate NUMBER);
CONNECT SCOTT/TIGER

COL eventname FORMAT A10
COL eventdate FORMAT A12
COL username FORMAT A10
COL obj_type FORMAT A15
COL obj_name FORMAT A15
COL obj_owner FORMAT A10
SELECT eventname, eventdate, obj_type, obj_name, obj_owner, username, Srv_error
  FROM eventlog;

DROP TRIGGER trig4_ddl;
DROP TRIGGER trig4_before;
DROP TRIGGER trig4_after;
DROP TABLE eventlog;
DROP TABLE mydata;

8.6 数据库触发器的应用实例

用户可以使用数据库触发器实现各种功能：

l 复杂的审计功能；

例：将EMP 表的变化情况记录到AUDIT_TABLE和AUDIT_TABLE_VALUES中。

CREATE TABLE audit_table(
    Audit_id     NUMBER,
    User_name VARCHAR2(20),
    Now_time DATE,
    Terminal_name VARCHAR2(10),
    Table_name VARCHAR2(10),
    Action_name VARCHAR2(10),
    Emp_id NUMBER(4));

CREATE TABLE audit_table_val(
    Audit_id NUMBER,
    Column_name VARCHAR2(10),
    Old_val NUMBER(7,2),
    New_val NUMBER(7,2));

CREATE SEQUENCE audit_seq
    START WITH 1000
    INCREMENT BY 1
    NOMAXVALUE
    NOCYCLE NOCACHE;

CREATE OR REPLACE TRIGGER audit_emp
    AFTER INSERT OR UPDATE OR DELETE ON emp
    FOR EACH ROW
DECLARE
    Time_now DATE;
    Terminal CHAR(10);
BEGIN
    Time_now:=sysdate;
    Terminal:=USERENV(‘TERMINAL‘);
    IF INSERTING THEN
        INSERT INTO audit_table
    VALUES(audit_seq.NEXTVAL, user, time_now,
           terminal, ‘EMP‘, ‘INSERT‘, :new.empno);
    ELSIF DELETING THEN
        INSERT INTO audit_table
    VALUES(audit_seq.NEXTVAL, user, time_now,
           terminal, ‘EMP‘, ‘DELETE‘, :old.empno);
    ELSE
        INSERT INTO audit_table
    VALUES(audit_seq.NEXTVAL, user, time_now,
           terminal, ‘EMP‘, ‘UPDATE‘, :old.empno);
        IF UPDATING(‘SAL‘) THEN
            INSERT INTO audit_table_val
                VALUES(audit_seq.CURRVAL, ‘SAL‘, :old.sal, :new.sal);
        ELSE UPDATING(‘DEPTNO‘)
            INSERT INTO audit_table_val
                VALUES(audit_seq.CURRVAL, ‘DEPTNO‘, :old.deptno, :new.deptno);
        END IF;
    END IF;
END;

l 增强数据的完整性管理；

例：修改DEPT表的DEPTNO列时，同时把EMP表中相应的DEPTNO也作相应的修改；

CREATE SEQUENCE update_sequence
    INCREMENT BY 1
    START WITH 1000
    MAXVALUE 5000 CYCLE;

ALTER TABLE emp
    ADD update_id NUMBER;

CREATE OR REPLACE PACKAGE integritypackage AS
    Updateseq NUMBER;
END integritypackage;

CREATE OR REPLACE PACKAGE BODY integritypackage AS
END integritypackage;

CREATE OR REPLACE TRIGGER dept_cascade1
    BEFORE UPDATE OF deptno ON dept
DECLARE
    Dummy NUMBER;
BEGIN
    SELECT update_sequence.NEXTVAL INTO dummy FROM dual;
    Integritypackage.updateseq:=dummy;
END;

CREATE OR REPLACE TRIGGER dept_cascade2
    AFTER DELETE OR UPDATE OF deptno ON dept
    FOR EACH ROW
BEGIN
    IF UPDATING THEN
        UPDATE emp SET deptno=:new.deptno,
     update_id=integritypackage.updateseq
        WHERE emp.deptno=:old.deptno AND update_id IS NULL;
    END IF;
    IF DELETING THEN
        DELETE FROM emp
            WHERE emp.deptno=:old.deptno;
    END IF;
END;

CREATE OR REPLACE TRIGGER dept_cascade3
    AFTER UPDATE OF deptno ON dept
BEGIN
    UPDATE emp SET update_id=NULL
        WHERE update_id=integritypackage.updateseq;
END;

SELECT * FROM EMP ORDER BY DEPTNO;
UPDATE dept SET deptno=25 WHERE deptno=20;

l 帮助实现安全控制；

例：保证对EMP表的修改仅在工作日的工作时间；

CREATE TABLE company_holidays(day DATE);

INSERT INTO company_holidays
    VALUES(sysdate);
INSERT INTO company_holidays
VALUES(TO_DATE(‘21-10月-01‘, ‘DD-MON-YY‘));

CREATE OR REPLACE TRIGGER emp_permit_change
    BEFORE INSERT OR DELETE OR UPDATE ON emp
DECLARE
    Dummy NUMBER;
    Not_on_weekends EXCEPTION;
    Not_on_holidays EXCEPTION;
    Not_working_hours EXCEPTION;
BEGIN
    /* check for weekends */
IF TO_CHAR(SYSDATE, ‘DAY‘) IN (‘星期六‘, ‘星期日‘) THEN
    RAISE not_on_weekends;
END IF;
    /* check for company holidays */
SELECT COUNT(*) INTO dummy FROM company_holidays
    WHERE TRUNC(day)=TRUNC(SYSDATE);
IF dummy >0 THEN
    RAISE not_on_holidays;
END IF;
    /* check for work hours(8:00 AM to 18:00 PM */
IF (TO_CHAR(SYSDATE,‘HH24‘)<8 OR TO_CHAR(SYSDATE, ‘HH24‘)>18) THEN
  RAISE not_working_hours;
END IF;
EXCEPTION
  WHEN not_on_weekends THEN
    RAISE_APPLICATION_ERROR(-20324,
‘May not change employee table during the weekends‘);
  WHEN not_on_holidays THEN
    RAISE_APPLICATION_ERROR(-20325,
‘May not change employee table during a holiday‘);
  WHEN not_working_hours THEN
    RAISE_APPLICATION_ERROR(-20326,
‘May not change employee table during no_working hours‘);
END;

本文出自 “IT精英的集中区” 博客，请务必保留此出处http://msuccess.blog.51cto.com/5486886/1416447

声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉：投诉/举报工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

联系
我们

首页 > 代码库 > ORACLE PL/SQL编程之八（二）： 把触发器说透

ORACLE PL/SQL编程之八（二）： 把触发器说透

看完仍有疑问？有类似问题直接问程序猿

首页 > 代码库 > ORACLE PL/SQL编程之八（二）：把触发器说透

ORACLE PL/SQL编程之八（二）：把触发器说透