导航：首页 > 数据库 >

Oracle专题16之触发器

发表于：2025-01-22 作者：千家信息网编辑

千家信息网最后更新 2025年01月22日，1、触发器的概述a、触发器是什么？触发器是指存放在数据库中，并且被隐含执行的存储过程。当发生特定事件时，Oracle会自动执行触发器的相应代码。b、触发器的类型DML触发器；DDL触发器；替代（ins

千家信息网最后更新 2025年01月22日Oracle专题16之触发器

1、触发器的概述

a、触发器是什么？

触发器是指存放在数据库中，并且被隐含执行的存储过程。
当发生特定事件时，Oracle会自动执行触发器的相应代码。
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 createdSQL> SET SERVEROUTPUT ONSQL> DELETE FROM emp01 WHERE empno = 7782;这是删除操作！1 row deleted

2、DDL触发器

a、什么是DDL触发器？

当创建、修改或者删除数据库对象时，也会引起相应的触发器操作事件，而此时就可以利用触发器来对这些数据库对象的DDL操作进行监控。
b、创建DDL触发器的语法格式
```
CREATE [OR REPLACE] TRIGGER 触发器的名称[BEFORE  | AFTER | INTEAD OF ] [DDL事件] ON [DATABASE | SCHEMA] [WHEN 触发条件] [DECLARE]     [程序的声明部分;] BEGIN    程序的代码部分END;/
```
c、DDL事件描述和触发时机
d、代码示例1：禁止scott用户的触发器操作

禁止scott用户的DDL操作

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 createdSQL> CREATE SEQUENCE test_seq;CREATE SEQUENCE test_seqORA-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：实现对数据库对象操作的日志记录

分解成三个步骤：
1、创建数据库对象DDL操作日志记录表；2、创建实现对数据库对象DDL操作记录的触发器；3、测试。

SQL> connect system/02000059 as sysdba;Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 Connected as system AS SYSDBASQL> 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 createdSQL> CREATE SEQUENCE object_log_seq;  Sequence createdSQL> 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 createdSQL> CREATE SEQUENCE test_seq;  --测试Sequence createdSQL> 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：实现数据安全保护（数据的安全性检查）
示例：禁止在休息日（周六、周日）改变emp表的数据。

分析：1、使用to_char(sysdate, 'day')函数；2、采用语句触发器。

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 createdSQL> DELETE FROM emp WHERE empno = 7369;DELETE FROM emp WHERE empno = 7369ORA-20006: 不能在休息日改变员工信息！ORA-06512: 在 "SCOTT.EMP_TRIGGER", line 3ORA-04088: 触发器 'SCOTT.EMP_TRIGGER' 执行过程中出错

f、示例2：实现数据审计

示例：审计员工信息表数据的变化，审计删除时间，以及被删除的雇员名。

使用SQL WINDOW窗口：（逐步执行）

--创建审计表CREATE TABLE delete_emp_audit(             name VARCHAR2(10),             delete_time DATE);--创建触发器CREATE OR REPLACE TRIGGER del_emp_triggerAFTER DELETE ON empFOR EACH ROWBEGIN    INSERT INTO delete_emp_audit VALUES(:old.ename, SYSDATE); --插入被删除员工的姓名和当前时间END;--测试DELETE FROM emp WHERE empno = 7499;SELECT * FROM delete_emp_audit;

在SQL语句和PLSQL语句中，old和new伪记录变量需要加上冒号:；而在WEHN这样的限制性条件语句当中，则不需要加上冒号:。
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 createdSQL> UPDATE emp SET sal = sal * 1.8 WHERE empno = 7902;UPDATE emp SET sal = sal * 1.8 WHERE empno = 7902ORA-20028: 工资直升不降，并且升幅不能超过50%ORA-06512: 在 "SCOTT.TR_CHECK_SAL", line 2ORA-04088: 触发器 'SCOTT.TR_CHECK_SAL' 执行过程中出错

h、示例4：实现参照完整性（比如级联更新）

为了实现级联删除，可以在定义外部键约束时指定ON DELETE CASCADE关键字。
但是使用约束却不能实现级联更新，为了实现级联更新，需要使用触发器。

示例：级联更新DEPT表的主键以及EMP表的外部键列。

CREATE OR REPLACE TRIGGER upd_cascade_triggerAFTER UPDATE OF deptnoON deptFOR 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, 'DEVELOPMENT', 2222, 'ALICE')ORA-01779: 无法修改与非键值保存表对应的列

3、创建emp_dept视图（复杂视图）的替代触发器：

CREATE OR REPLACE TRIGGER instead_of_triggerINSTEAD OF INSERTON emp_deptFOR EACH ROWDECLARE    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, 'DEVELOPMENT', 2222, '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
```