---触发器

触发器在数据库里以独立的对象存储

触发器是由一个事件来启动运行

触发器常用来完成由数据库的完整性约束难以完成的复杂业务规则的约束，或用来监视对数据库的各种操作，实现审计的功能。

---触发器分类

1. DML触发器

2.替代触发器

3.系统触发器

---另外一种分法:

语句级(STATEMENT)触发器

行级(ROW)触发器

---另外一种分法:

before

after

------行级触发器专用

:NEW 修饰符访问操作完成后列的值

:OLD 修饰符访问操作完成前列的值

 

:old.deptno 删除之前的数据

:new.deptno 删除之后的数据

--1. 替代(INSTEAD OF)触发器----只能应用在行级上面，无论有没有 for each row

只能用在视图上

CREATE OR REPLACE VIEW v_emp AS

SELECT deptno, count(*) total_emp, sum(sal) total_sal

FROM emp

GROUP BY deptno;---简单视图和原表中的数据是一对一的

 

 

select * from  v_emp;

 

rollback

delete from v_emp where deptno=10;-----复杂视图不能做变更，使用替代触发器

 

CREATE OR REPLACE TRIGGER tr_vemp_del

  INSTEAD OF DELETE ON v_emp

  FOR EACH ROW

BEGIN

  DELETE FROM emp WHERE deptno = :old.deptno;

END tr_vemp_del;

/

DELETE FROM v_emp WHERE deptno=10;

--2.DML触发器

---语句级触发器

CREATE OR REPLACE TRIGGER tr_dept_time

  BEFORE INSERT OR DELETE OR UPDATE ON dept

BEGIN

  IF (TO_CHAR(sysdate, 'DAY') IN ('星期六', '星期日')) OR

     (TO_CHAR(sysdate, 'HH24:MI') NOT BETWEEN '08:00' AND '18:00') THEN

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

  END IF;

END;

/

 

 delete from dept where deptno=10;

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

 ----行级触发器

CREATE OR REPLACE TRIGGER tr_emp_sal_comm

  BEFORE UPDATE OF sal, comm OR DELETE ON scott.emp

  FOR EACH ROW

  WHEN (old.deptno = 10)

BEGIN

  CASE

    WHEN UPDATING('sal') THEN

      IF :NEW.sal < :old.sal THEN

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

      END IF;

    WHEN UPDATING('comm') THEN

      IF :NEW.comm < :old.comm THEN

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

      END IF;

    WHEN DELETING THEN

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

  END CASE;

END;

select * from emp;

UPDATE emp SET sal = 2000 WHERE empno = 7839;

UPDATE emp SET sal = 2    WHERE empno = 7788;

 

---INSTEAD OF触发器需要注意以下几点：

1.只能被创建在视图上，并且该视图没有指定WITH CHECK OPTION选项。

2.不能指定BEFORE 或 AFTER选项。

3.FOR EACH ROW子句是可选的，即INSTEAD OF触发器只能在行级上触发、或只能是行级触发器，没有必要指定。---为了可读性更好，可以加上

-----3. 系统事件触发器

CREATE TABLE ddl_event

(crt_date timestamp PRIMARY KEY,

 event_name VARCHAR2(20),

 user_name VARCHAR2(10),

 obj_type VARCHAR2(20),

 obj_name VARCHAR2(20));

 

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 table tt(id number);

 

 select * from ddl_event;

 SELECT * FROM log_event;

 CREATE TABLE log_event

(user_name VARCHAR2(10),

 address VARCHAR2(20),

 logon_date timestamp,

 logoff_date timestamp);

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;

/

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;

 

 

CREATE OR REPLACE TRIGGER tr_nologon

  BEFORE LOGOFF ON DATABASE

BEGIN

  INSERT INTO log_event

    (user_name, address, logon_date)

  VALUES

    (ora_login_user, ora_client_ip_address, systimestamp);

END tr_nologon;

 

 

 

重新编译触发器

ALTER TRIGGER [schema.] trigger_name COMPILE;

DROP TRIGGER trigger_name;

禁用或启用触发器:

ALTER TIGGER trigger_name [DISABLE | ENABLE ];

DISABLE  失效

ENABLE 生效

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

 

ALTER TABLE emp DISABLE ALL TRIGGERS;

 

 

SELECT TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT,

 TABLE_OWNER, BASE_OBJECT_TYPE, REFERENCING_NAMES,

 STATUS, ACTION_TYPE

FROM user_triggers;