CREATE OR REPLACE TRIGGER tr_del_emp
   BEFORE DELETE --指定觸發(fā)時(shí)機(jī)為刪除操作前觸發(fā)
   ON scott.emp
   FOR EACH ROW   --說(shuō)明創(chuàng)建的是行級(jí)觸發(fā)器
BEGIN
   --將修改前數(shù)據(jù)插入到日志記錄表 del_emp ,以供監(jiān)督使用。
   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的人員的獎(jiǎng)金不能降');
        END IF;
     WHEN DELETING THEN
          RAISE_APPLICATION_ERROR(-20003, '不能刪除部門80的人員記錄');
     END CASE;
END;
  
/*
實(shí)例：
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, '不是上班時(shí)間，不能修改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;

--創(chuàng)建觸犯發(fā)器
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;