触发器

2024-02-20 16:19:10

什么是触发器（trigger）来自百度百科
触发器（trigger）是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。触发器可以从 DBA_TRIGGERS ，USER_TRIGGERS 数据字典中查到。SQL3的触发器是一个能由系统自动执行对数据库修改的语句。
触发器可以查询其他表，而且可以包含复杂的SQL语句。它们主要用于强制服从复杂的业务规则或要求。例如：您可以根据客户当前的帐户状态，控制是否允许插入新订单。
触发器也可用于强制引用完整性，以便在多个表中添加、更新或删除行时，保留在这些表之间所定义的关系。然而，强制引用完整性的最好方法是在相关表中定义主键和外键约束。如果使用数据库关系图，则可以在表之间创建关系以自动创建外键约束。
触发器与存储过程的唯一区别是触发器不能执行EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发执行。
此外触发器是逻辑电路的基本单元电路，具有记忆功能，可用于二进制数据储存，记忆信息等。

触发器4个基本组成：
(1)触发事件：即在什么事件发生时触发，常见事件如 insert ,update,delete;
(2)触发时间：这里的时间是相对于事件发生前后来说的，可以定义在事件发生之前和事件发生之后 before | after
(3)触发器本身：这是关键，也就是我们为什么要创建这个触发器，要用它做什么，触发器的主体，一段pl/sql程序块
(4)触发器频率：说明触发器内，在事件发生时，触发的频率，常见有语句级触发器和行级触发器，语句级就是该条语句执行一次，触发器触发一次，而行级触发器则是每操作一行就执行一次触发器，定义时需要添加（for each row）

触发器语法

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE| AFTER}
{INSERT| UPDATE| DELETE}
ON table_name
[FOR EACH ROW]
[WHERE CONDITION]
BEGIN
  执行体;
END;

CREATE OR REPLACE TRIGGER TABLE_NAME_TRIGGER
AFTER
INSERT OR UPDATE 
ON TABLE_NAME
FOR EACH ROW
BEGIN
 your sql1;
 your sql2;
END;

CREATE OR REPLACE TRIGGER TABLE_NAME_TRIGGER ：创建一个名为TABLE_NAME_TRIGGER的trigger，trigger名通常为table_name_trigger，表明这个trigger是建立在哪张表上
AFTER 表示执行条件，有 BEFORE（之前） AFTER（之后）
INSERT OR UPDATE 表示在执行了插入操作 ,有INSERT/UPDATE/DELETE 三种
ON TABLE_NAME：触发这个trigger的表
AFTER INSERT OR UPDATE ON TABLE_NAME：触发这个trigger的事件
FOR EACH ROW:这是一个行触发器
BEGIN 和 END 之间定义这个触发器要执行的操作

DDL和DML语句

DDL语句(用来操作表)：DDL语句用于定义和管理数据库中的对象，如Create,Alter，Drop，truncate等；DDL操作是隐性提交的！操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger。
DML(操作表数据)：DML(Data Manipulation Language)数据操纵语言命令使用户能够查询数据库以及操作已有数据库中的数据。如增删改查insert,delete,update,select等都是DML.

触发器事务
触发器和触发它的DML是同一个事务,DML提交了，触发器的操作也提交了，要不就一起回滚了。在正常情况下，Oracle规定在触发器中不能运行 DDL(即Create/Alter/Drop)语句和Commit/Rollback语句的，因为DDL操作是隐性提交的，在触发器不允许有Commit，如在触发器中加入DDL语句，这种隐性提交就会导致错误信息。

特殊情况下需要在触发器中使用DDL语句，需要用到Oracle中的自治事务
在可以在触发器中加入：pragma autonomous_transaction表示是*事务处理，自治事务就相当于一个事务里的子事务。如：

CREATE OR REPLACE TRIGGER TABLE_NAME_TRIGGER
AFTER INSERT OR UPDATE ON TABLE_NAME
FOR EACH ROW
DECLARE
pragma autonomous_transaction;----在DECLARE后面) 表示是*事务处理
BEGIN
 your ddl sql;
END;

通过以上方法即可解决触发器中不能有DDL语句的问题！

当然，如果需要在触发器里写COMMIT，也可以用Oracle中的自治事务来处理
为了把触发器功能和触发该触发器的DML语句分成两个单独的事务，在触发器中加入：pragma autonomous_transaction表示自治事务处理，自治事务就相当于一个事务里的子事务，然后再自己显示提交触发器事务

CREATE OR REPLACE TRIGGER TABLE_NAME_TRIGGER
AFTER INSERT OR UPDATE ON TABLE_NAME
FOR EACH ROW
DECLARE
pragma autonomous_transaction;----在DECLARE后面) 表示是*事务处理
BEGIN
 your dml sql;
 commit;
END;

Oracle 触发器分类

触发器分为语句级触发器（又叫做表级触发器）和行级触发器。

选择语句级触发器还是行级触发器的依据如下：行触发器要求当一个DML语句操作影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激活一次触发器；而语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器。当省略FOR EACH ROW 选项时，BEFORE 和AFTER 触发器为语句触发器，而INSTEAD OF 触发器则只能为行触发器。如果一条INSERT语句在TABLE表中插入500行，那么这个表上的语句级触发器只执行一次，而行级触发器就要执行500次了。

行触发器

行触发器有 for each row子句
可以有 when 作为触发限制
提供new和old对象，new 代表修饰符访问操作完成后的值，old 代表修饰符访问操作完成前的值; new和old只出现在insert和update的sql语句中，在insert中时new表示新插入的行数据，在update中时new表示要替换的新数据、old表示要被更改的原来的数据行，在delete中时old表示要被删除的数据。
不得将Insert/Update/Delete语句作用于自身数据表
对应DML语句所影响到的表中的每一行，触发器都要执行一遍。如果一条INSERT语句在TABLE表中插入500行，那么这个表上的语句级触发器只执行一次，而行级触发器就要执行500次了。

语句(表)触发器

语句触发器没有for each row 子句
语句触发器不能有when 作为触发限制
在表触发器中，不支持 :new 和 :old对象
对应DML语句所影响到的表中的所有行，触发器只执行一遍。如果一条INSERT语句在TABLE表中插入500行，那么这个表上的语句级触发器只执行一次，而行级触发器就要执行500次了。

Oracle 一个表上的多个触发器的执行顺序
before statement trigger->before row trigger->after row trigger->after statement trigger。

触发器可用的业务场景
需要更新一个功能，但是又不想动到原先功能的后台代码。

业务场景: 在触发器中，需要修改到其它行的数据(我没试过)
参考 ora 04091：表XX发生了变化，触发器/函数不能读它(lin49940)
思路：表A 的after update 触发器 -> 存数据到中间表B(注意要把new 的关键数据保存) -> 建立表B 的after insert 触发器(加上自治事务, 查询表A 数据要把new 相关的记录排除, 再通过union 组合, 不然会读到脏数据, 对表A 进行操作)

业务场景：对A表进行insert或update时，把这条insert或update的数据读入B表中

参考關於ORA-04091異常的出現原因，以及解決方案
假设有业务需求如下：TABLEA表和TABLEB表都有column：id,CATEGORY_KEY，CODE_KEY，VALUE_TEXT,LAST_MODIFY_BY,LAST_MODIFY_TIME。当对A表进行insert或update时，需要把这条insert或update的数据读入B表中。对于这种需求，就可以用行触发器的new和old来实现。Trigger:

CREATE OR REPLACE TRIGGER TB_TABLEA_TRIGGER 
AFTER 
INSERT OR UPDATE ON TB_TABLEA
FOR EACH ROW
BEGIN
 insert into TB_TABLEB(id,CATEGORY_KEY，CODE_KEY，VALUE_TEXT,LAST_MODIFY_BY,LAST_MODIFY_TIME)   values(:new.id,:new.CATEGORY_KEY,:new.CODE_KEY,:new.VALUE_TEXT,:new.LAST_MODIFY_BY,:new.LAST_MODIFY_TIME);
END;

Oracle触发器修改自身表

参考 oracle 触发器中修改基表解决办法
如果一个触发器定义在表a上，那么在触发器被触发的过程中表a是被锁定的，就连读取都不可以。所以如果触发器要修改自身表，就需要用到行触发器提供的:old和:new对象
在insert或者update这张表的某条数据时，把更新这条数据的LAST_MODIFY_BY和LAST_MODIFY_TIME。思路就是在insert或者update这条数据之前，就准备好LAST_MODIFY_BY和LAST_MODIFY_TIME的new值，只是准备new值，然后用触发该触发器的DML来执行insert或者update操作

create or replace TRIGGER TB_TABLEA_TRIGGER 
BEFORE INSERT OR UPDATE ON TB_TABLEA 
FOR EACH ROW
BEGIN
  :new.LAST_MODIFY_BY := 'username';
  :new.LAST_MODIFY_TIME := SYSDATE;
END;

码农公寓

相关文章