1、触发器的概述
a、触发器是什么？
b、触发器的类型
- DML触发器；DDL触发器；替代（instead of）触发器；系统触发器。
c、触发器的组成
1、触发事件：即在何种情况下触发TRIGGER。 2、触发时间：即该TRIGGER是在触发事件发生之前（BEFORE）还是之后（AFTER）触发。 3、触发器本身：即该TRIGGER被触发之后的目的和意图，正是触发器本省要做的事情。 4、触发频率：说明触发器内定义的动作被执行的次数。
d、创建第一个触发器
sql> --当对emp01表执行删除操作之后，它就会出现一个提示信息，提示：这是删除操作 sql> CREATE TRIGGER first_trigger 2 AFTER DELETE 3 ON emp01 4 BEGIN 5 DBMS_OUTPUT.put_line('这是删除操作！'); 6 END; 7 / Trigger created sql> SET SERVEROUTPUT ON sql> DELETE FROM emp01 WHERE empno = 7782; 这是删除操作！ 1 row deleted
2、DDL触发器
a、什么是DDL触发器？
b、创建DDL触发器的语法格式
CREATE [OR REPLACE] TRIGGER 触发器的名称 [BEFORE | AFTER | INTEAD OF ] [DDL事件] ON [DATABASE | SCHEMA] [WHEN 触发条件] [DECLARE] [程序的声明部分;] BEGIN 程序的代码部分 END; /
c、DDL事件描述和触发时机
d、代码示例1：禁止scott用户的触发器操作
sql> CREATE OR REPLACE TRIGGER scott_trigger 2 BEFORE DDL 3 ON SCHEMA 4 BEGIN 5 RAISE_APPLICATION_ERROR(-20005,'scott用户禁止所有的DDL操作'); 6 END; 7 / Trigger created sql> CREATE SEQUENCE test_seq; CREATE SEQUENCE test_seq ORA-00604: 递归 sql 级别 1 出现错误 ORA-20005: scott用户禁止所有的DDL操作 ORA-06512: 在 line 2
- 注意：RAISE_APPLICAITON_ERROR是用来测试的异常处理，能够将应用程序专有的错误从服务器端转达到客户端应用程序中（其他机器上的sqlPlus或者其他前台开发语言），其存储过程有两个参数，如：
RAISE_APPLICAITON_ERROR(error_number_in IN NUMBER,error_msg_in IN VARCHAR2); -- error_msg_in的长度不能超过2k，超过2k后会进行截取
e、代码示例2：实现对数据库对象操作的日志记录
sql> connect system/02000059 as sysdba; Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 Connected as system AS SYSDBA sql> CREATE TABLE object_log( --创建数据库对象DDL操作日志记录表 2 logid number constraint pk_logid primary key,3 operatedate date not null,4 objecttype varchar2(50) not null,5 objectowner varchar2(50) not null); Table created sql> CREATE SEQUENCE object_log_seq; Sequence created sql> CREATE OR REPLACE TRIGGER ojbect_trigger --创建实现对数据库对象DDL操作记录的触发器 2 AFTER CREATE OR DROP OR ALTER 3 ON DATABASE 4 BEGIN 5 INSERT INTO object_log(logid,operatedate,objecttype,objectowner) VALUES (object_log_seq.nextval,sysdate,ora_dict_obj_type,ora_dict_obj_owner); 6 END; 7 / Trigger created sql> CREATE SEQUENCE test_seq; --测试 Sequence created sql> select * from object_log; LOGID OPERATEDATE OBJECTTYPE OBJECTOWNER ---------- ----------- -------------------------------------------------- -------------------------------------------------- 1 2017/12/21 SEQUENCE SYS
- 注意：此DDL触发器事件示例中的使用了两个属性函数（之前未使用）：
- ORA_DICT_OBJ_OWNER：触发DDL的数据库对象的用户；ORA_DICT_OBJ_TYPE：触发DDL的数据库对象的类型。
3、DML触发器
a、什么是DML触发器？
- DML触发器是指基于DML操作所建立的触发器。
b、DML触发器的作用
- DML触发器可用于实现数据安全保护、数据审计、数据完整性、参照完整性、数据复制等功能。
c、DML触发器类型
- 包括语句触发器和行触发器。 1、语句触发器：在指定操作语句之前或者之后执行一次，不管这条语句影响了多少行。（针对行） 2、行触发器：触发语句作用的每一条记录都被触发，在行级触发器中使用:old和:new伪记录变量，识别值的状态。（针对表）
- :old表示操作该行之前，这一行的值；:new 表示操作该行之后，这一行的值。
d、创建DML触发器的语法格式
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER}
{DELETE | INSERT | UPDATE [ OF 列名]}
ON 表名
[FOR EACH ROW [WHEN (条件）])
PLsql块
e、示例1：实现数据安全保护（数据的安全性检查）
sql> CREATE OR REPLACE TRIGGER emp_trigger 2 BEFORE INSERT OR UPDATE OR DELETE 3 ON emp 4 BEGIN 5 IF to_char(sysdate,'day') IN ('星期六','星期日') THEN 6 RAISE_APPLICATION_ERROR(-20006,'不能在休息日改变员工信息！'); 7 END IF; 8 END; 9 / Trigger created sql> DELETE FROM emp WHERE empno = 7369; DELETE FROM emp WHERE empno = 7369 ORA-20006: 不能在休息日改变员工信息！ ORA-06512: 在 "SCOTT.EMP_TRIGGER",line 3 ORA-04088: 触发器 'SCOTT.EMP_TRIGGER' 执行过程中出错
f、示例2：实现数据审计
--创建审计表 CREATE TABLE delete_emp_audit( name VARCHAR2(10),delete_time DATE ); --创建触发器 CREATE OR REPLACE TRIGGER del_emp_trigger AFTER DELETE ON emp FOR EACH ROW BEGIN INSERT INTO delete_emp_audit VALUES(:old.ename,SYSDATE); --插入被删除员工的姓名和当前时间 END; --测试 DELETE FROM emp WHERE empno = 7499; SELECT * FROM delete_emp_audit;
g、示例3：实现数据完整性（数据确认）
- 数据完整性用于确保数据满足商业逻辑或者企业规则。
- 实现数据完整性首选约束，约束无法实现的，可以使用触发器实现数据完整性。
- 示例：比如要求员工涨后工资不能低于原来的工资，并且所涨的工资不能超过原工资的50%。
sql> CREATE OR REPLACE TRIGGER tr_check_sal 2 BEFORE UPDATE OF sal ON emp 3 FOR EACH ROW 4 WHEN (new.sal < old.sal OR new.sal > old.sal * 1.5) 5 BEGIN 6 RAISE_APPLICATION_ERROR(-20028,'工资直升不降，并且升幅不能超过50%'); 7 END; 8 / Trigger created sql> UPDATE emp SET sal = sal * 1.8 WHERE empno = 7902; UPDATE emp SET sal = sal * 1.8 WHERE empno = 7902 ORA-20028: 工资直升不降，并且升幅不能超过50% ORA-06512: 在 "SCOTT.TR_CHECK_SAL",line 2 ORA-04088: 触发器 'SCOTT.TR_CHECK_SAL' 执行过程中出错
h、示例4：实现参照完整性（比如级联更新）
- 为了实现级联删除，可以在定义外部键约束时指定ON DELETE CASCADE关键字。
- 但是使用约束却不能实现级联更新，为了实现级联更新，需要使用触发器。
- 示例：级联更新DEPT表的主键以及EMP表的外部键列。
CREATE OR REPLACE TRIGGER upd_cascade_trigger AFTER UPDATE OF deptno ON dept FOR EACH ROW BEGIN UPDATE emp SET deptno = :new.deptno WHERE deptno = :old.deptno; END; --测试 UPDATE dept SET deptno = 50 WHERE deptno = 10; SELECT deptno,ename FROM emp WHERE deptno = 50;
4、INSTEAD OF触发器
a、什么是INSTEAD OF触发器（替代触发器）？
- 适用于视图上的触发器
b、为什么使用INSTEAD OF触发器？
- 在简单视图上往往可以执行INSET、UPDATE、DELETE操作。
- 但是在复杂视图上执行INSET、UPDATE、DELETE操作时有限制。所以为了在这些复杂的视图上执行DML操作，需要建立替代触发器。
c、INSTEAD OF 触发器的限制
- 替代触发器只适用于视图。
- 替代触发器不能指定BEFORE和AFTER选项。
- 不能在具有WITH CHECK OPTION选项的视图上建立替代触发器。
- 替代触发器必须包含FOR EACH ROW选项。
d、示例代码
1、创建emp_dept视图：
sql> CREATE OR REPLACE VIEW emp_dept 2 AS 3 SELECT d.deptno,d.dname,e.empno,e.ename 4 FROM dept d,emp e 5 WHERE d.deptno = e.deptno; View created
2、当没有创建替代触发器时，对emp_dept视图插入数据出错：
sql> INSERT INTO emp_dept VALUES(50,'DEVELOPMENT',2222,'ALICE'); INSERT INTO emp_dept VALUES(50,'ALICE') ORA-01779: 无法修改与非键值保存表对应的列
3、创建emp_dept视图（复杂视图）的替代触发器：
CREATE OR REPLACE TRIGGER instead_of_trigger INSTEAD OF INSERT ON emp_dept FOR EACH ROW DECLARE v_temp INT; BEGIN SELECT COUNT(*) INTO v_temp FROM dept WHERE deptno = :new.deptno; IF v_temp = 0 THEN INSERT INTO dept(deptno,dname) VALUES(:new.deptno,:new.dname); END IF; SELECT COUNT(*) INTO v_temp FROM emp WHERE empno = :new.empno; IF v_temp = 0 THEN INSERT INTO emp(empno,ename,deptno) VALUES(:new.empno,:new.ename,:new.deptno); END IF; END;
4、对emp_dept视图进行插入操作：
sql> INSERT INTO emp_dept VALUES(50,'ALICE'); 1 row inserted sql> SELECT * FROM EMP WHERE empno = 2222; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 2222 ALICE 50 sql> SELECT *FROM DEPT WHERE deptno = 50; DEPTNO DNAME LOC ------ -------------- ------------- 50 ACCOUNTING NEW YORK sql> SELECT * FROM emp_dept WHERE empno = 2222; DEPTNO DNAME EMPNO ENAME ------ -------------- ----- ---------- 50 ACCOUNTING 2222 ALICE
5、系统触发器
a、什么是系统触发器？
- 系统触发器是由特定系统事件所触发的触发器。（需要注意的是，系统触发器是要有系统用户来创建的）
- 系统事件是指与例程或者方案相关的数据库事件，它包括STARTUP、SHUTDOWN、DB_ROLE_CHANGE和SERVERERROR的四种事件。 1、STARTUP事件触发器是在启动数据库后触发。 2、SHUTDOWN事件触发器在关闭数据之前触发。 3、DB_ROLE_CHANGE事件触发器在改变角色后第一次打开数据库时触发。 4、SERVERERROR事件触发器在发生Oracle错误时触发。
b、示例代码
1、创建记录发生的数据库系统EVENT_TALBE事件表：
CREATE TABLE event_table( event VARCHAR2(50),event_time date );
2、创建系统触发器：
sql> CREATE OR REPLACE TRIGGER startup_trigger 2 AFTER STARTUP ON DATABASE 3 BEGIN 4 INSERT INTO event_table VALUES(ORA_SYSEVENT,SYSDATE); 5 END; 6 / Trigger created原文链接：https://www.f2er.com/oracle/206694.html
