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 INSERTBEFORE INSERT FOR EACH ROWAFTER INSERTAFTER INSERT FOR EACH ROW BEFORE UPDATEBEFORE UPDATE FOR EACH ROWAFTER UPDATEAFTER UPDATE FOR EACH ROW BEFORE DELETEBEFORE DELETE FOR EACH ROWAFTER DELETEAFTER 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_deleteINSTEAD OF DELETE ON emp_view FOR EACH ROWBEGINDELETE 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_logonAFTER LOGON ON DATABASEBEGIN 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_logoffBEFORE LOGOFF ON DATABASEBEGIN INSERT INTO log_event (user_name, address, logoff_date) VALUES (ora_login_user, ora_client_ip_address, systimestamp);END tr_logoff;
posted on 2018-10-22 22:41 阅读( ...) 评论( ...)