Oracle 触发器(一)

1）触发器是一种特殊的存储过程，触发器一般由事件触发并且不能接受参数，存储器由语句块去调用；触发器是当某个事件发生时自动地隐式运行。

2）触发器分类：

　　1.DML触发器： 创建在表上，由DML事件引发

　　2.替代触发器（insteadof）：由于视图有可能是由多个表进行关联而成，直接通过更新视图来更新基表是不可行的，那我们就可以通过替代触发器来工作了。

　　3.DDL触发器： 触发事件时数据库对象的创建和修改

　　4.数据库事件触发器：定义在数据库或者模式上，由数据库事件触发

      或者如下分类：

　　1.行触发器：数据库表中的每一行有变化都会触发一次触发器代码

　　2.语句触发器：与语句所影响的行数无关，仅触发一次

　　3.BEFORE触发器：在DML语句执行之前触发

　　4.ALFTER触发器：在DML语句执行之后触发

3）组成：  

　　1.触发事件：引发触发器被触发的事件 DML语句(INSERT, UPDATE, DELETE语句对表或视图执行数据处理操作)、DDL语句（如CREATE、ALTER、DROP语句在数据库中创建、修改、删除模式对象）、数据库系统事件（如系统启动或退出、异常错误）、用户事件（如登录或退出数据库）。

　　2.触发时间：即该触发器是在触发事件发生之前（BEFORE）还是之后(AFTER)触发

　　3.触发操作：触发器触发后要完成的事情

　　4.触发对象：包括表、视图、模式、数据库。只有在这些对象上发生了符合触发条件的触发事件，触发器才会执行触发操作。

　　5.触发条件：由WHEN子句指定一个逻辑表达式。只有当该表达式的值为TRUE时，遇到触发事件才会自动执行触发操作。

　　6.触发频率：说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。（比如delete多条数据时，行级触发器可能会执行多次，语句级触发器只会触发一次）　　

4）、注意：

1、触发器不能接收参数

2、一张表最多可以有12个触发器

BEFORE INSERT
BEFORE INSERT FOR EACH ROW
AFTER INSERT
AFTER INSERT FOR EACH ROW
 
BEFORE UPDATE
BEFORE UPDATE FOR EACH ROW
AFTER UPDATE
AFTER UPDATE FOR EACH ROW
 
BEFORE DELETE
BEFORE DELETE FOR EACH ROW
AFTER DELETE
AFTER DELETE FOR EACH ROW

3、触发器最大为32k

4、触发器中不能使用数据库事务控制语句，并且由触发器所调用的过程或函数也不能使用数据库事务控制语句，如commit、rollback

5、触发器中不能使用Long

 二、语法说明

1、DML触发器：DML触发器指的是在对表进行增删改操作引发的自动执行事件。

--DML触发器基本定义：
　　CREATE  [OR REPLACE] TRIGGER  [user.] trigger
　　{BEFORE|AFTER}  {INSERT | DELETE | UPDATE [OF column [, column …]]}
　　[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...] on
 [user.]table
　　[FOR EACH ROW]
　　[WHEN (condition)]
　　BEGIN
　　　　.................
　　END;

其中：

　　BEFORE 和AFTER指出触发器的触发时序分别为前触发和后触发方式。
　　FOR EACH ROW选项说明触发器为行触发器。
　　在行触发器的PL/SQL块和WHEN 子句中可以使用相关名称参照当前的新、旧列值，默认的相关名称分别为OLD和NEW。触发器的PL/SQL块中应用相关名称时，必须在它们之前加冒号(:)，但在WHEN子句中则不能加冒号。

　　WHEN 子句说明触发约束条件。

 Demo1:

记录每条记录的插入时间(10g后可以在建表时用rowdependencies实现等级追踪实现效果同样)

--先创建日志记录表 
CREATE TABLE SZ_RECORD_TEST
 ("SZID" VARCHAR2(1024) NOT NULL,
 "SZVERSION" VARCHAR2(1024), 
"EVENT_TIME" DATE DEFAULT sysdate NOT NULL); --定义event_time的默认值是sysdate；
/

--创建触发器 
CREATE OR REPLACE TRIGGER TRG_SZD --触发器名称
 AFTER INSERT ON TT_SZD  --在TT_SZD表插入表之后出发
 FOR EACH ROW  --行级触发器
BEGIN 
    INSERT INTO SZ_RECORD_TEST(SZID,SZVERSION) 
    VALUES (:NEW.SZD_ID, :NEW.VERSION); 
END; 

Demo2:

限制对Departments表修改（包括INSERT,DELETE,UPDATE）的时间范围，即不允许在非工作时间修改departments表

 

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; 
/*可能不是很多人知道 RAISE_APPLICATION_ERROR 的用途是什么，虽然从字面上已经猜到这个函数是干什么用的。平时用来测试的异常处理
我们都是通过dbms_output.put_line来输出异常信息，但是在实际的应用中，需要把异常信息返回给调用的客户端。
其实 RAISE_APPLICATION_ERROR 是将应用程序专有的错误从服务器端转达到客户端应用程序(其他机器上的SQLPLUS或者其他前台开发语言)
RAISE_APPLICATION_ERROR 的声明：
PROCEDURE RAISE_APPLICATION_ERROR( error_number_in IN NUMBER, error_msg_in IN VARCHAR2);
里面的错误代码和内容，都是自定义的。说明是自定义，当然就不是系统中已经命名存在的错误类别，是属于一种自定义事务错误类型，才调用此函数。
error_number_in 之容许从 -20000 到 -20999 之间，这样就不会与 ORACLE 的任何错误代码发生冲突。error_msg_in 的长度不能超过 2k，否则截取 2k。*/

 Demo3：

　限定只对部门号为80的记录进行行触发器操作。

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;             

 2、替代触发器（insteadof）：由于视图有可能是由多个表进行关联而成，直接通过更新视图来更新基表是不可行的，那我们就可以通过替代触发器来工作了。

 demo4:

--准备操作
INSERT INTO DEPT(DEPTNO,DNAME,LOC) VALUES(60,'研发部','上海');
/
INSERT INTO EMP(EMPNO, ENAME,JOB,
                MGR,HIREDATE,SAL,
                COMM,DEPTNO)
              VALUES(1001,'贾宝玉','程序员',7788,TO_DATE ('2013-02-03','YYYY-MM-DD'),8000,1000,60);
/             
CREATE VIEW emp_info_view
 AS 
 SELECT E.EMPNO,E.ENAME,E.JOB,E.SAL,D.DNAME,D.LOC
 FROM EMP e JOIN DEPT d ON e.deptno=d.deptno ;
 /
 SELECT * FROM emp_info_view;
 
--创建替代触发器
create or replace trigger emp_info_view_trigger 
instead of update  
    on emp_info_view for each row  
 begin  
  update dept set dname=:new.dname,loc=:new.loc
  where dname=:new.dname;  
 end; 
--更新视图
/
update emp_info_view set loc='杭州' where dname='研发部'
--查询结果
/
SELECT * FROM dept；

CREATE OR REPLACE VIEW emp_view AS 
SELECT deptno, count(*) total_employeer, sum(sal) total_salary 
FROM emp GROUP BY deptno;
/
在此视图中直接删除是非法：
SQL>DELETE FROM emp_view WHERE deptno=10;
DELETE FROM emp_view WHERE deptno=10
*
ERROR 位于第 1 行:
ORA-01732: 此视图的数据操纵操作非法

--但是我们可以创建INSTEAD_OF触发器来为 DELETE 操作执行所需的处理，即删除EMP表中所有基准行：
/
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;
/
DELETE FROM emp_view WHERE deptno=10;

 3、DDL触发器

DDL触发器，当执行DDL语句时会被触发。按照作用范围，分为schema triggers,database triggers。schema triggers作用在一个用户上，database triggers作用在整个数据库所有用户上。

创建DDL触发器

要创建一个DDL触发器，语法如下：

CREATE [OR REPLACE] TRIGGER trigger name                         --创建一个触发器并制定名称，or replace是可选项 
 {BEFORE | AFTER } { DDL event} ON {DATABASE | SCHEMA}     --指定触发器是在DDL事件之前、之后触发。范围是on database、on schema 
 [WHEN (...)]                                                                               --可选的WHEN子句，使用逻辑判断来避免触发器无意义的执行 
 DECLARE                                                                                  --触发器具体内容4-7
 Variable declarations 
BEGIN 
 ...some code... 
END;
Examples:
SQL> CREATE OR REPLACE TRIGGER hr.testtrigger 
 AFTER CREATE ON SCHEMA  -- on schema 作用范围只是在hr用户下create table等触发，其他用户则不会。若是on database则其他用户create table时会触发该触发器 
 BEGIN 
   -- 以下使用的是事件属性 
 DBMS_OUTPUT.PUT_LINE('I believe you have created a ' || 
 ORA_DICT_OBJ_TYPE || ' called ' || 
 ORA_DICT_OBJ_NAME); 
 END; 
 / 
Trigger created.

可用事件

                                            可用的DDL事件

DDL事件	触发时机
ALTER	对数据库中的任何一个对象使用SQL的ALTER命令时触发
ANALYZE	对数据库中的任何一个对象使用SQL的ANALYZE命令时触发
ASSOCIATE STATISTICS	统计数据关联到数据库对象时触发
AUDIT	通过SQL的AUDIT命令打开审计时触发
COMMENT	对数据库对象做注释时触发
CREATE	通过SQL的CREATE命令创建数据库对象时触发
DDL	列表中所用的事件都会触发
DISASSOCIATE STATISTICS	去掉统计数据和数据库对象的关联时触发
DROP	通过SQL的DROP命令删除数据库对象时触发
GRANT	通过SQL的GRANT命令赋权时触发
NOAUDIT	通过SQL的NOAUDIT关闭审计时触发
RENAME	通过SQL的RENAME命令对对象重命名时触发
REVOKE	通过SQL的REVOKE语句撤销授权时触发
TRUNCATE	通过SQL的TRUNCATE语句截断表时触发

可用属性

Oracle 提供了一系列的函数用来提供关于什么触发了DDL触发器以及触发器的状态灯信息。上面那个触发器的例子就使用了属性。

                                                       DDL触发器事件以及属性函数

函数名	返回值
ORA_CLIENT_IP_ADDRESS	客户端IP地址
ORA_DATABASE_NAME	数据库名称
ORA_DES_ENCRYPTED_PASSWORD	当前用户的DES算法加密后的密码
ORA_DICT_OBJ_NAME	触发DDL的数据库对象名称
ORA_DICT_OBJ_NAME_LIST	受影响的对象数量和名称列表
ORA_DICT_OBJ_OWNER	触发DDL的数据库对象属主
ORA_DICT_OBJ_OWNER_LIST	受影响的对象数量和名称列表
ORA_DICT_OBJ_TYPE	触发DDL的数据库对象类型
ORA_GRANTEE	被授权人数量
ORA_INSTANCE_NUM	数据库实例数量
ORA_IS_ALTER_COLUMN	如果操作的参数column_name指定的列，返回true,否则false
ORA_IS_CREATING_NESTED_TABLE	如果正在创建一个嵌套表则返回true,否则false
ORA_IS_DROP_COLUMN	如果删除的参数column_name指定的列，返回true,否则false
ORA_LOGIN_USER	触发器所在的用户名
ORA_PARTITION_POS	SQL命令中可以正确添加分区子句位置
ORA_PRIVILEGE_LIST	授予或者回收的权限的数量。
ORA_REVOKEE	被回收者的数量
ORA_SQL_TXT	触发了触发器的SQL语句的行数。
ORA_SYSEVENT	导致DDL触发器被触发的时间
ORA_WITH_GRANT_OPTION	如果授权带有grant选项，返回true。否则false

更多属性函数请参考官方文档PL/SQL Language Reference -> Triggers and Oracle Database Data Transfer Utilities

使用事件和属性

Examples:

--创建数据库对象时发出警告，删除数据库对象时阻止

CREATE OR REPLACE TRIGGER HR.no_drop 
BEFORE DDL ON DATABASE 
BEGIN 
IF ORA_SYSEVENT = 'CREATE' 
THEN 
DBMS_OUTPUT.PUT_LINE('Warning !!! You have created a '|| 
                        ORA_DICT_OBJ_TYPE ||' called '|| 
                        ORA_DICT_OBJ_NAME|| '; UserName(creater):'|| 
                        ORA_DICT_OBJ_OWNER||'; IP:'|| 
                        ORA_CLIENT_IP_ADDRESS||'; event:'|| 
                        ORA_SYSEVENT);                        
ELSIF ORA_SYSEVENT = 'DROP' 
THEN 
RAISE_APPLICATION_ERROR (-20000, 
'Cannot create the ' || ORA_DICT_OBJ_TYPE || 
' named ' || ORA_DICT_OBJ_NAME || 
' as requested by ' || ORA_DICT_OBJ_OWNER); 
END IF; 
END;

--操作了数据库表的哪一列

CREATE OR REPLACE TRIGGER preserve_app_cols 
   AFTER ALTER ON SCHEMA 
DECLARE 
   -- cursor to get columns in a table 
   CURSOR curs_get_columns (cp_owner VARCHAR2, cp_table VARCHAR2) 
   IS 
      SELECT column_name 
        FROM all_tab_columns 
       WHERE owner = cp_owner AND table_name = cp_table; 
BEGIN 
   -- if it was a table that was altered... 
   IF ora_dict_obj_type = 'TABLE' 
   THEN 
      -- for every column in the table... 
      FOR v_column_rec IN curs_get_columns ( 
                             ora_dict_obj_owner, 
                             ora_dict_obj_name 
                          ) 
      LOOP 
         -- if the current column was the one that was altered then say so 
         IF ora_is_alter_column (v_column_rec.column_name) 
         THEN 
            -- if the table/column is core? 
            IF is_application_column ( 
                  ora_dict_obj_owner, 
                  ora_dict_obj_name, 
                  v_column_rec.column_name 
               ) 
            THEN 
               RAISE_APPLICATION_ERROR ( 
                  -20001, 
                  'Cannot alter core application attributes' 
               ); 
            END IF; -- table/column is core 
         END IF; -- current column was altered 
      END LOOP; -- every column in the table 
   END IF; -- table was altered 
END; 

--属性函数返回值列表

CREATE OR REPLACE TRIGGER hr.what_privs 
   AFTER GRANT ON SCHEMA 
DECLARE 
   v_grant_type     VARCHAR2 (30); 
   v_num_grantees   BINARY_INTEGER; 
   v_grantee_list   ora_name_list_t; 
   v_num_privs      BINARY_INTEGER; 
   v_priv_list      ora_name_list_t; 
BEGIN 
   v_grant_type := ora_dict_obj_type; 
   v_num_grantees := ora_grantee (v_grantee_list); 
   v_num_privs := ora_privilege_list (v_priv_list);
   IF v_grant_type = 'ROLE PRIVILEGE' 
   THEN 
      DBMS_OUTPUT.put_line ( 
         CHR (9) || 'The following roles/privileges were granted' 
      );
      FOR counter IN 1 .. v_num_privs 
      LOOP 
         DBMS_OUTPUT.put_line ( 
            CHR (9) || CHR (9) || 'Privilege ' || v_priv_list (counter) 
         ); 
      END LOOP; 
   ELSIF v_grant_type = 'OBJECT PRIVILEGE' 
   THEN 
      DBMS_OUTPUT.put_line ( 
         CHR (9) || 'The following object privileges were granted' 
      );
      FOR counter IN 1 .. v_num_privs 
      LOOP 
         DBMS_OUTPUT.put_line ( 
            CHR (9) || CHR (9) || 'Privilege ' || v_priv_list (counter) 
         ); 
      END LOOP;
      DBMS_OUTPUT.put (CHR (9) || 'On ' || ora_dict_obj_name);
      IF ora_with_grant_option 
      THEN 
         DBMS_OUTPUT.put_line (' with grant option'); 
      ELSE 
         DBMS_OUTPUT.put_line (''); 
      END IF; 
   ELSIF v_grant_type = 'SYSTEM PRIVILEGE' 
   THEN 
      DBMS_OUTPUT.put_line ( 
         CHR (9) || 'The following system privileges were granted' 
      );
      FOR counter IN 1 .. v_num_privs 
      LOOP 
         DBMS_OUTPUT.put_line ( 
            CHR (9) || CHR (9) || 'Privilege ' || v_priv_list (counter) 
         ); 
      END LOOP; 
   ELSE 
      DBMS_OUTPUT.put_line ('I have no idea what was granted'); 
   END IF;
   FOR counter IN 1 .. v_num_grantees 
   LOOP 
      DBMS_OUTPUT.put_line ( 
         CHR (9) || 'Grant Recipient ' || v_grantee_list (counter) 
      ); 
   END LOOP; 
END; 

 demo:

--创建登录、退出触发器。

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_logoff
BEFORE LOGOFF ON DATABASE
BEGIN
   INSERT INTO log_event (user_name, address, logoff_date)
   VALUES (ora_login_user, ora_client_ip_address, systimestamp);
END tr_logoff;