search-icon search-icon

#触发器

触发器（Trigger）是数据库里的一种PL对象。创建一个触发器即创建了一个可执行的过程体，但与存储过程和函数不同的是，触发器过程体不可以被用户显式调用，而是由系统依据某个事件来触发执行。

一个触发器包含如下要素：

  • 触发操作：触发执行的内容，为一个过程体。
  • 触发事件：可以由系统判断的触发过程体执行的事件，即对表的INSERT/UPDATE/DELETE等DML操作。 可以定义单个触发事件，也可以定义多个触发事件的组合（OR逻辑组合） 。
  • 触发时机： 触发过程体执行的时间点，包括BEFORE（触发事件发生前执行）和AFTER（触发事件发生后执行）两种。
  • 触发对象：触发事件所基于的对象，即某张表。
  • 触发类型：包括语句级触发（触发事件发生时，执行一次过程体）和行级触发（触发事件发生时，对其影响的每一行数据均执行一次过程体）两种类型。
  • 触发条件：对于行级触发器，可以由WHEN语句指定一个条件表达式，在触发事件发生且条件表达式结果为true时，过程体才会被执行。

在表上定义触发器可以在对该表执行DML操作时，及时地进行一些错误拦截，或者操作记录，或者业务逻辑处理，相比存储过程的优势是实时性以及可以获得当时刻的数据信息，但其会对DML操作产生关联影响，如执行效率或本身问题导致DML操作失败等。

除触发器外，另一个在对表执行DML操作即被触发的功能为约束。约束是为了保证数据完整性而执行的字段级别的数据检查，相比约束，触发器使用的过程性语句可以实现更复杂的数据处理。对一个同时定义了约束和触发器的表执行DML操作时，系统的处理顺序为：

  1. 执行BEFORE语句级触发器。

  2. 对DML操作影响的每一行：

    2.1 执行BEFORE行级触发器。

    2.2 执行DML操作，同时执行非FOREIGN KEY的约束项检查。

    2.3 执行AFTER行级触发器。

    2.4 执行FOREIGN KEY检查。

  3. 执行AFTER语句级触发器。

触发器仅适用于HEAP表。

# 创建触发器

包括触发器的定义和编译，如果触发器编译失败，依然会被创建，但是会在执行时出错。执行失败的触发器会阻止DML语句事件上的所有触发器，直到它被禁用、删除、或被重建且编译成功。

使用CREATE TRIGGER语句创建触发器，其语法定义为：

create_trigger::=

syntax
CREATE OR REPLACE EDITIONABLE NONEDITIONABLE TRIGGER schema . trigger_name simple_dml_trigger

simple_dml_trigger::=

syntax
BEFORE AFTER dml_event_clause referencing_clause FOR EACH ROW trigger_ordering_clause ENABLE DISABLE WHEN condition trigger_body_clause

dml_event_clause::=

syntax
DELETE INSERT UPDATE OF column , OR ON schema . table

referencing_cause::=

syntax
REFERENCING OLD AS old NEW AS new OLD AS old NEW AS new

trigger_ordering_clause::=

syntax
FOLLOWS schema . trigger ,

trigger_body_clause::=

syntax
DECLARE variable_declare BEGIN plsql_statements END

# or replace

当要创建的触发器已经存在时，将其进行重建。

# editionable | noneditionable

用于语法兼容，无实际含义。

# schema

包含触发器的模式名称，可省略，则默认为当前登录用户的模式。

# trigger_name

要创建的触发器的名称，在同一模式下的触发器不能有相同的名称。不可省略，且需符合YashanDB的对象命名规范。

# simple_dml_trigger

本语句用于定义触发器的各项要素。

# before|after

定义触发时机，在触发事件发生之前（之后）运行触发器。对于行级触发器，触发器在操作每个受影响的行之前（之后）运行。

# dml_event_clause

定义触发事件及触发对象。

触发器在表上创建，其触发事件可以是DML语句DELETE、INSERT、UPDATE的OR组合。

对于MERGE语句，可以通过在MERGE分解后的INSERT、UPDATE、DELETE语句上分别创建触发器，以实现对MERGE语句的触发操作。

条件谓词

由于触发事件可以由多个触发语句组合，当某一条语句导致触发事件发生时，触发器可以通过下述条件谓词来识别出具体是哪一条语句。

条件谓词 是TRUE的场景
INSERTING 由INSERT语句触发
UPDATING 由UPDATE语句触发
UPDATING ('column') 由指定列的UPDATE语句触发
DELETING 由DELETE语句触发
# delete

从表中删除数据时运行触发器。

# insert

向表中添加数据时运行触发器。

# update [ of column [, column ] ]

更改指定列字段的值时运行触发器。未指定列字段时，更改表的任何列的值均会运行触发器。

不能指定为LOB类型的列字段。

# schema

要在其上创建触发器的表的模式名称。可省略，则默认为当前登录用户的模式。

不能为SYS模式下的对象创建触发器。

# table

要在其上创建触发器的表的名称。

不能为私有临时表创建触发器。

# referencing_clause

系统提供当前数据的新旧值供引用，默认使用OLD/NEW来识别，也可以通过本语句为其定义新的名称。当系统中存在名称为OLD或NEW的表对象时，应该通过本语句定义新的名称，以避免由于名称混淆出现错误。

# for each row

定义触发器为行级触发器，表示系统将为触发事件影响并满足触发条件的每一行数据执行一次触发器。 省略此子句时，触发器默认定义为语句级触发器，表示系统将在触发事件发生时执行一次触发器。

# trigger_order_clause

当为某个对象的某个触发事件定义了多个触发器时，使用本语句可以指定它们的相对运行关系。

FOLLOWS命令表示要创建的触发器必须在指定的触发器之后运行。指定的触发器必须存在，并且已成功编译，但是不需要启用。

# enable|disable

指定创建的触发器为启用或禁用状态，不指定则默认为启用。

# when (condition)

定义触发条件，只针对行级触发器，当在行上计算的condition为真时，则为这一行运行触发器。

需注意的是，触发条件影响的是触发器的运行，而不会影响触发事件，即DML语句的运行，无论触发条件结果为true还是false，DML语句都将执行。

condition的语法与通用SQL语法condition一致，但不能包含子查询和自定义函数。其中，对于列字段的引用需要按OLD/NEW（或由referencing_clause定义的名称）区分新旧值，引用格式为OLD.column，NEW.column。

# trigger_body_clause

定义触发操作，即一个过程体。

# declare variable_declare

声明PL过程体的全局变量，可省略。

PL变量描述。

# pl_statements

定义过程体中的执行语句，为PL语句中的一项或多项，但任何语句或由语句调用的子程序，均不可以包含DDL和DCL语句内容。

其中，对于触发对象的列字段的引用需要按OLD/NEW（或由referencing_clause定义的名称）区分新旧值，引用格式及取值定义见下表：

:OLD.column :NEW.column
INSERT NULL INSERT的值
DELETE DELETE前的值 NULL
UPDATE UPDATE前的值 UPDATE后的值

对于新旧值的引用还存在如下规则：

  • 不能在语句级触发器中读写':NEW'和':OLD'，包括BEFORE和AFTER。
  • 在BEFORE行级触发器中可读或写':NEW'，只能读':OLD'。
  • 在AFTER行级触发器中只能读':OLD'和':NEW'，不能写':OLD'和':NEW'。
  • 不能引用udt列，包括object列的属性。

示例（HEAP表）

-- 在product表上创建行级前触发器t，在INSERT、DELETE、UPDATE时分别打印不同的信息。
-- 为old和new指定新的相关性名称。
CREATE OR REPLACE TRIGGER tri 
    BEFORE 
    INSERT OR UPDATE OF product_name, price OR DELETE
    ON product 
    REFERENCING OLD AS told NEW AS tnew
    FOR EACH ROW
    ENABLE
    WHEN (told.product_no<>11003 OR tnew.product_no <> 11003)
BEGIN
    IF INSERTING THEN
        DBMS_OUTPUT.PUT_LINE('Inserting. id is ' || :tnew.product_no);
    ELSIF DELETING THEN
        DBMS_OUTPUT.PUT_LINE('Deleting. id is ' || :told.product_no);
    ELSIF UPDATING('price') THEN
        DBMS_OUTPUT.PUT_LINE('Updating price. id is ' || :tnew.product_no);
        DBMS_OUTPUT.PUT_LINE('Updating old price:' || :told.price);
        DBMS_OUTPUT.PUT_LINE('Updating new price:' || :tnew.price);
        :tnew.price := :tnew.price + 10;
        DBMS_OUTPUT.PUT_LINE('Updating final price:' || :tnew.price);
    ELSE
        DBMS_OUTPUT.PUT_LINE('Updating Others. id is ' || :tnew.product_no);
    END IF;
END;
/

--执行DML语句
--1.主键约束项在BEFORE行级触发器之后运行
INSERT INTO product VALUES ('11001','product001',8,10);
Inserting. id is 11001
YAS-02030 unique constraint violated
--2.when条件为false，触发器不运行，但DML语句仍运行
DELETE product WHERE product_no=11001;
Deleting. id is 11001
ROLLBACK;
--3.更新price
UPDATE product SET price=20 WHERE product_no=11002;
Updating price. id is 11002
Updating old price:16
Updating new price:20
Updating final price:30

# 创建自治事务触发器

在上述创建触发器的语法中，由于触发事件的DML语句和触发操作的过程体处于同一事务中，所以在过程体中不允许执行DCL和DDL语句，以避免错误地提交或回滚。

当存在必须要提交事务的情况，例如触发器用于操作记录，记录的日志表为另一张独立表且在获取到触发事件的新旧值、系统时间等信息就应该及时提交，此时可以通过创建自治事务触发器来将触发操作独立成一个事务来运行。

定义自治事务触发器的语句为：pragma autonomous_transaction;。

示例（HEAP表）

--创建记录日志表
CREATE TABLE product_log 
(product_no CHAR(5),
 update_date DATE,
 newprice NUMBER,
 oldprice NUMBER);
 
-- 自治事务触发器。在 product 表上创建行级前触发器 log_product，在更新 product.price 列时将旧值和新值写入product_log表。
CREATE OR REPLACE TRIGGER log_product 
    BEFORE 
    DELETE 
    ON product 
    FOR EACH ROW
DECLARE
PRAGMA autonomous_transaction;
BEGIN
    DBMS_OUTPUT.PUT_LINE('old.price is ' || :old.price);
    DBMS_OUTPUT.PUT_LINE('new.price is ' || :NEW.price);
    INSERT INTO product_log VALUES (:old.product_no, SYSDATE, :NEW.price, :old.price);
    COMMIT;
END;
/

-- 删除product表的数据，触发log_product及tri，日志表将独立提交
DELETE product WHERE product_no=11002;
Deleting. id is 11002

-- 回滚DML操作，product数据被恢复，但日志表仍记录当时操作
ROLLBACK;
SELECT price FROM product WHERE product_no=11002;
      PRICE 
----------- 
         30
SELECT PRODUCT_NO,UPDATE_DATE,NEWPRICE,OLDPRICE FROM product_log  WHERE product_no=11002;
PRODUCT_NO UPDATE_DATE                         NEWPRICE    OLDPRICE 
---------- -------------------------------- ----------- ----------- 
11002      2022-06-26                                            16

# 启用/禁用触发器

使用ALTER TRIGGER语句可以启用或禁用一个已创建的触发器。

# 删除触发器

使用DROP TRIGGER语句可以删除一个已存在的触发器。

此外，当触发器的触发对象，即表被删除（DROP TABLE）时，该触发器也将被删除。

pdf-btn 下载文档
copy-btn 复制链接
段落导航
创建触发器
or replace
editionable | noneditionable
schema
trigger_name
simple_dml_trigger
before|after
dml_event_clause
delete
insert
update [ of column [, column ] ]
schema
table
referencing_clause
for each row
trigger_order_clause
enable|disable
when (condition)
trigger_body_clause
declare variable_declare
pl_statements
创建自治事务触发器
启用/禁用触发器
删除触发器