Oracle中的触发器trigger

CREATE OR REPLACE TRIGGER tr_del_emp

    BEFORE DELETE --指定触发时机为删除操作前触发

    ON scott.emp

    FOR EACH ROW   --说明创建的是行级触发器

BEGIN

    --将修改前数据插入到日志记录表 del_emp ,以供监督使用。

    INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate )

        VALUES ( :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :old.comm, :old.hiredate );

END ;

CREATE OR REPLACE TRIGGER tr_reg_cou

AFTER update OF region_id

ON regions

FOR EACH ROW

BEGIN

  DBMS_OUTPUT.PUT_LINE( '旧的region_id值是' ||:old.region_id

                   || '、新的region_id值是' ||:new.region_id);

  UPDATE countries SET region_id = :new.region_id

  WHERE region_id = :old.region_id;

END ;

CREATE OR REPLACE TRIGGER tr_emp_sal_comm

BEFORE UPDATE OF salary, commission_pct

        OR DELETE

ON HR.employees

FOR EACH ROW

WHEN (old.department_id = 80)

BEGIN

  CASE

      WHEN UPDATING ( 'salary' ) THEN

         IF :NEW.salary < :old.salary THEN

            RAISE_APPLICATION_ERROR(-20001, '部门80的人员的工资不能降' );

         END IF;

      WHEN UPDATING ( 'commission_pct' ) THEN

         IF :NEW.commission_pct < :old.commission_pct THEN

            RAISE_APPLICATION_ERROR(-20002, '部门80的人员的奖金不能降' );

         END IF;

      WHEN DELETING THEN

           RAISE_APPLICATION_ERROR(-20003, '不能删除部门80的人员记录' );

      END CASE ;

END ;

/*

实例：

UPDATE employees SET salary = 8000 WHERE employee_id = 177;

DELETE FROM employees WHERE employee_id in (177,170);

*/

CREATE OR REPLACE TRIGGER tr_dept_time

BEFORE INSERT OR DELETE OR UPDATE

ON departments

BEGIN

  IF (TO_CHAR(sysdate, 'DAY' ) IN ( '星期六' , '星期日' )) OR (TO_CHAR(sysdate, 'HH24:MI' ) NOT BETWEEN '08:30' AND '18:00' ) THEN

      RAISE_APPLICATION_ERROR(-20001, '不是上班时间，不能修改departments表' );

  END IF;

END ;

CREATE OR REPLACE TRIGGER emp_view_delete

    INSTEAD OF DELETE ON emp_view FOR EACH ROW

BEGIN

    DELETE FROM emp WHERE deptno= :old.deptno;

END emp_view_delete;

--创建触犯发器

CREATE OR REPLACE TRIGGER tr_ddl

AFTER DDL ON SCHEMA

BEGIN

    INSERT INTO ddl_event VALUES

    (systimestamp,ora_sysevent, ora_login_user,

     ora_dict_obj_type, ora_dict_obj_name);

END tr_ddl;

CREATE OR REPLACE TRIGGER tr_logon

AFTER LOGON ON DATABASE

BEGIN

    INSERT INTO log_event (user_name, address, logon_date)

    VALUES (ora_login_user, ora_client_ip_address, systimestamp);

END tr_logon;

ALTER TRIGGER   trigger   COMPILE

DROP TRIGGER trigger_name;

ALTER TRIGGER emp_view_delete DISABLE| ENABLE;

--使表EMP 上的所有TRIGGER 失效：

ALTER TABLE emp DISABLE ALL TRIGGERS;