PL/SQL_触发器（触发器简介）

2021-11-29 06:41:40

触发器是指存放在数据库中，并被隐含执行的存储过程。在Oracle8i之前，只允许基于表或视图的DML操作(INSERT，UPDATE和DELETE）建立触发器；而从Oracle8i开始，不仅支持DML触发器，也允许基于系统事件（启动数据库、关闭数据库、登录）和DDL操作建立触发器。

触发器简介

1、触发事件
触发事件是指引起触发器被触发的SQL语句、数据库事件或用户事件。在Oracle8i之前，触发事件只能是DML操作；而从Oracle8i开始，不仅支持原有的DML事件，而且还增加了其他触发事件。具体的触发事件如下：
1）启动和关闭例程
2）Oracle错误消息
3）用户登录和断开会话
4）特定表或视图的DML操作

5）在任何方案上的DDL语句

2、触发条件（可选）
触发条件是指使用WHEN子句指定一个BOOLEAN表达式，当布尔表达式返回值为TRUE时，会自动执行触发器相应代码；当布尔表达式返回值为FALSE或UNKNOWN时，不会执行触发操作。
3、触发操作
触发操作是指包含SQL语句和其他执行代码的PL/SQL块，不仅可以使用PL/SQL进行开发，也可以使用Java语言和C语言进行开发。但编写触发器执行代码时，需要注意以下限制：
1）触发器代码的大小不能超过32K。如果确实需要使用大量代码建立触发器，应该首先建立存储过程，然后在触发器中使用CALL语句调用存储过程。
2）触发器只能包含DML语句，而不能包含DDL语句（CREATE,ALTER，DROP）和事务控制语句（COMMIT，ROLLBACK和SAVEPOINT）。

建立DML触发器

当建立DML触发器时，需要指定触发时机（BEFORE或AFTER）、触发事件（INSERT，UPDATE，DELETE）、表名、触发类型、触发条件以及触发操作。
1、触发时机
触发时机用于指定触发器的触发时间。当指定BEFORE关键字时，表示在执行DML操作之前触发触发器；当指定AFTER关键字时，表示在执行了DML操作之后触发触发器。
2、触发事件
触发事件用于指定导致触发器执行的DML操作，也即INSERT，UPDATE和DELETE操作。即可以使用单个触发事件，也可以组合多个触发事件。
3、表名
因为DML触发器是针对特定表进行的，所以必须指定DML操作所对应的表。
4、触发类型
触发类型用于指定触发事件发生之后，需要执行几次触发操作。如果指定语句触发类型（默认），那么只会执行一次触发器代码；如果指定行触发类型，则会在每个被作用行上执行一次触发器代码。
5、触发条件
触发条件用于指定执行触发器代码的条件，只有条件为TRUE时才会执行触发器代码。注意，当编写DML触发器时，只允许在行触发器上指定触发条件。
6、触发操作
触发操作用于指定触发器执行代码。
7、DML触发器触发顺序
（1）DML触发器在单行数据上的触发顺序
（2）DML触发器在多行数据上的触发顺序

语句触发器

当审计DML操作，或者确保DML操作安全执行时，可以使用语句触发器。注意，使用语句触发器时，不能记录列数据的变化。建立语句触发器的语法如下：

CREATE [OR REPLACE] TRIGGER trigger_name
timing event1 [OR event2 OR event3]
ON table_name
PL/SQL block;

如上所示，trigger_name用于指定触发器名；timing用于指定触发时机（BEFORE或AFTER）；event角于指定触发事件（INSERT，UPDATE和DELETE）；table_name用于指定DML操作所对应的表名。
1、建立BEFORE语句触发器
为了禁止工作人员在休息日改变雇员信息，开发人员可以建立BEFORE语句触发器，以实现数据的安全保护。

CREATE OR REPLACE TRIGGER tr_sec_emp
BEFORE INSERT OR UPDATE OR DELETE
ON emp
BEGIN
  IF to_char(SYSDATE,‘D‘) IN (1,7) THEN
    RAISE_APPLICATION_ERROR(-20001,‘不能在休息日改变雇员信息‘);
  END IF;
END;

2、使用条件谓词
当在触发器中同时包含多个触发事件（INSERT、UPDATE、DELETE）时，为了在触发器代码中区分具体的触发事件，可以使用以下三个条件谓词：
1）INSERTING：当触发事件是INSERT操作时，该条件谓词返回值为TRUE，否则为FALSE
2）UPDATING：当触发事件是UPDATE操作时，该条件谓词返回值为TRUE，否则为FALSE。
3）DELETING：当触发事件是DELETE操作时，该条件谓词返回值为TRUE，否则为FALSE。
示例如下：

CREATE OR REPLACE TRIGGER tr_sec_emp
BEFORE INSERT OR UPDATE OR DELETE
ON emp
BEGIN
  IF to_char(SYSDATE,‘D‘) IN (1,7) THEN
    CASE
      WHEN INSERTING THEN
        RAISE_APPLICATION_ERROR(-20001,‘不能在休息日增加雇员‘);
      WHEN DELETING THEN
        RAISE_APPLICATION_ERROR(-20002,‘不能在休息日解雇雇员‘);
      WHEN UPDATING THEN
        RAISE_APPLICATION_ERROR(-20003,‘不能在休息日更新雇员‘);
    END CASE;
  END IF;
END;

3、建立AFTER语句触发器
在建立AFTER触发器之前，首先建立审计表audit_table。示例如下：

CREATE TABLE tbl_audit(
  NAME VARCHAR2(20),
  ins INTEGER,
  upd INTEGER,
  del INTEGER,
  starttime DATE,
  endtime DATE
);

为了审计在EMP表上DML操作执行的次数、最早执行时间和最近执行时间，需要建立AFTER语句触发器。示例如下：

CREATE OR REPLACE TRIGGER tr_audit_emp
AFTER INSERT OR DELETE OR UPDATE
ON emp
DECLARE
  v_temp INTEGER;
BEGIN
  SELECT COUNT(*) INTO v_temp FROM tbl_audit WHERE NAME=‘EMP‘;
  IF v_temp = 0 THEN
    INSERT INTO tbl_audit(NAME,ins,upd,del,starttime)
    VALUES (‘EMP‘,0,0,0,SYSDATE);
  END IF;
  CASE
    WHEN INSERTING THEN
      UPDATE tbl_audit SET ins = ins + 1,endtime = SYSDATE WHERE NAME = ‘EMP‘;
    WHEN DELETING THEN
      UPDATE tbl_audit SET del = del + 1,endtime = SYSDATE WHERE NAME = ‘EMP‘;
    WHEN UPDATING THEN
      UPDATE tbl_audit SET upd = upd + 1,endtime = SYSDATE WHERE NAME = ‘EMP‘;
  END CASE;
END;

测试：

UPDATE emp SET sal = 888 WHERE empno = 7788;
UPDATE emp SET sal = 999 WHERE empno = 1111;
SELECT * FROM tbl_audit;

PL/SQL_触发器（触发器简介）,布布扣,bubuko.com

PL/SQL_触发器（触发器简介）

码农公寓

触发器简介

建立DML触发器

语句触发器

相关文章