Oracle专题16之触发器

发布时间：2020-08-06 20:59:55 来源：网络阅读：664 作者：糖醋白糖栏目：关系型数据库

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 created

SQL> SET SERVEROUTPUT ON
SQL> 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 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：实现对数据库对象操作的日志记录

分解成三个步骤：
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 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：实现数据安全保护（数据的安全性检查）
示例：禁止在休息日（周六、周日）改变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 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：实现数据审计

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

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

--创建审计表
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;

在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 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, 'DEVELOPMENT', 2222, '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, '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
```

向AI问一下细节

Oracle专题16之触发器

1、触发器的概述

a、触发器是什么？

b、触发器的类型

c、触发器的组成

d、创建第一个触发器

2、DDL触发器

a、什么是DDL触发器？

b、创建DDL触发器的语法格式

c、DDL事件描述和触发时机

d、代码示例1：禁止scott用户的触发器操作

e、代码示例2：实现对数据库对象操作的日志记录

3、DML触发器

a、什么是DML触发器？

b、DML触发器的作用

c、DML触发器类型

d、创建DML触发器的语法格式

e、示例1：实现数据安全保护（数据的安全性检查）

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

g、示例3：实现数据完整性（数据确认）

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

4、INSTEAD OF触发器

a、什么是INSTEAD OF触发器（替代触发器）？

b、为什么使用INSTEAD OF触发器？

c、INSTEAD OF 触发器的限制

d、示例代码

5、系统触发器

a、什么是系统触发器？

b、示例代码

猜你喜欢

最新资讯

相关推荐

相关标签