oracle触发器（下篇）

最新推荐文章于 2023-09-09 08:22:46 发布

塔山上的山

最新推荐文章于 2023-09-09 08:22:46 发布

阅读量443

点赞数

分类专栏： oracle基础

本文链接：https://blog.csdn.net/li19236/article/details/41486061

版权

oracle基础专栏收录该内容

326 篇文章 3 订阅

订阅专栏

oracle触发器（下篇）

8.3 删除和使能触发器

l 删除触发器：

DROP TRIGGER trigger_name;

当删除其他用户模式中的触发器名称，需要具有DROP ANY TRIGGER系统权限，当删除建立在数据库上的触发器时，用户需要具有ADMINISTER DATABASE TRIGGER系统权限。

此外，当删除表或视图时，建立在这些对象上的触发器也随之删除。

l 禁用或启用触发器

数据库TRIGGER 的状态：

有效状态(ENABLE)：当触发事件发生时，处于有效状态的数据库触发器TRIGGER 将被触发。

无效状态(DISABLE)：当触发事件发生时，处于无效状态的数据库触发器TRIGGER 将不会被触发，此时就跟没有这个数据库触发器(TRIGGER) 一样。

数据库TRIGGER的这两种状态可以互相转换。格式为：

ALTER TIGGER trigger_name [ DISABLE | ENABLE ] ;

-- 例：ALTER TRIGGER emp_view_delete DISABLE;

ALTER TRIGGER语句一次只能改变一个触发器的状态，而ALTER TABLE语句则一次能够改变与指定表相关的所有触发器的使用状态。格式为：

ALTER TABLE [ schema. ] table_name {ENABLE | DISABLE} ALL TRIGGERS;

-- 例：使表EMP 上的所有TRIGGER 失效：
ALTER TABLE emp DISABLE ALL TRIGGERS;

8.4 触发器和数据字典

相关数据字典：USER_TRIGGERS、ALL_TRIGGERS、DBA_TRIGGERS

SELECT TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT,
TABLE_OWNER, BASE_OBJECT_TYPE, REFERENCING_NAMES,
STATUS, ACTION_TYPE
FROM user_triggers;

8.5 数据库触发器的应用举例

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

CREATE TABLE dept_summary(
Deptno NUMBER ( 2 ),
Sal_sum NUMBER ( 9 , 2 ),
Emp_count NUMBER );

INSERT INTO dept_summary(deptno, sal_sum, emp_count)
SELECT deptno, SUM (sal), COUNT ( * )
FROM emp
GROUP BY deptno;

-- 创建一个PL/SQL过程disp_dept_summary
-- 在触发器中调用该过程显示dept_summary标中的数据。
CREATE OR REPLACE PROCEDURE disp_dept_summary
IS
Rec dept_summary % ROWTYPE;
CURSOR c1 IS SELECT * FROM dept_summary;
BEGIN
OPEN c1;
FETCH c1 INTO REC;
DBMS_OUTPUT.PUT_LINE( ' deptno    sal_sum    emp_count ' );
DBMS_OUTPUT.PUT_LINE( ' ------------------------------------- ' );
WHILE c1 % FOUND LOOP
    DBMS_OUTPUT.PUT_LINE(RPAD(rec.deptno, 6 ) ||
      To_char(rec.sal_sum, ' $999,999.99 ' ) ||
      LPAD(rec.emp_count, 13 ));
     FETCH c1 INTO rec;
END LOOP;
CLOSE c1;
END ;
BEGIN
DBMS_OUTPUT.PUT_LINE( ' 插入前 ' );
Disp_dept_summary();
DBMS_UTILITY.EXEC_DDL_STATEMENT( '
    CREATE OR REPLACE TRIGGER trig1
      AFTER INSERT OR DELETE OR UPDATE OF sal ON emp
    BEGIN
      DBMS_OUTPUT.PUT_LINE( '' 正在执行trig1 触发器… '' );
      DELETE FROM dept_summary;
      INSERT INTO dept_summary(deptno, sal_sum, emp_count)
      SELECT deptno, SUM(sal), COUNT(*)
      FROM emp GROUP BY deptno;
    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 );

DBMS_OUTPUT.PUT_LINE( ' 插入后 ' );
Disp_dept_summary();

UPDATE emp SET sal = 1000 WHERE empno = 9999 ;
DBMS_OUTPUT.PUT_LINE( ' 修改后 ' );
Disp_dept_summary();

DELETE FROM emp WHERE empno = 9999 ;
DELETE FROM dept WHERE deptno = 90 ;

DBMS_OUTPUT.PUT_LINE( ' 删除后 ' );
Disp_dept_summary();
DBMS_UTILITY.EXEC_DDL_STATEMENT(‘ DROP TRIGGER trig1’);
EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE || ' --- ' || SQLERRM);

END ;

例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

IF TO_CHAR(SYSDATE, ' DAY ' ) IN ( ' 星期六 ' , ' 星期日 ' ) THEN
    RAISE not_on_weekends;
END IF ;

SELECT COUNT ( * ) INTO dummy FROM company_holidays
     WHERE TRUNC( day ) = TRUNC(SYSDATE);
IF dummy > 0 THEN
    RAISE not_on_holidays;
END IF ;

IF (TO_CHAR(SYSDATE, ' HH24 ' ) <</span>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;

l 管理复杂的表复制；

l 防止非法的事务发生；

l 自动生成派生的列值；

帮助式显复杂的商业管理。

原创作品，转贴请注明作者和出处，留此信息。

------------------------------------------------

cnBlobs:http://www.cnblogs.com/huyong/
CSDN：http://blog.csdn.net/chinahuyong

作者：EricHu（DB、C\S、B\S、WebService、WCF、PM等）
出处：http://www.cnblogs.com/huyong/

Q Q：80368704 E-Mail: 80368704@qq.com
本博文欢迎大家浏览和转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，在『参考』的文章中，我会表明参考的文章来源，尊重他人版权。若您发现我侵犯了您的版权，请及时与我联系。

塔山上的山

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle触发器（下篇）

oracle触发器（下篇）8.3 删除和使能触发器l 删除触发器：DROP TRIGGER trigger_name;当删除其他用户模式中的触发器名称，需要具有DROP ANY TRIGGER系统权限，当删除建立在数据库上的触发器时，用户需要具有ADMINISTER DATABASE TRIGGER系统权限。此外，当删除表或视图时，建立在这些对象上的触发器也随之删除
复制链接

扫一扫

专栏目录