触发器

触发器

触发器是一种特殊的存储过程，也具备事务的功能，它能在多表之间执行特殊的业务规则。

• 触发器是特定事件出现的时候，自动执行的代码块

• 类似于存储过程，触发器与存储过程的区别在于：存储过程是由用户或应用程序显式调用的，而触发器是不能被直接调用的

• 触发器是在对表进行插入、更新或删除操作时自动执行的存储过程，触发器通常用于强制业务规则

• 触发器还是一个特殊的事务单元，当出现错误时，可以执行ROLLBACK TRANSACTION 回滚撤销操作

• 触发器一般都需要使用临时表：deleted 表和inserted 表，它们存放了被删除或插入的记录行副本

• 触发器类型：DML触发器、DDL触发器

定义：触发器是在对指定表插入（insert）、更新（update）或删除（delete）操作时所自动执行的存储过程。

作用：实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。

触发器是一种高级约束，可以定义比check 约束更为复杂的约束。

• 可执行复杂的SQL语句

• 可引用其他表中的列

• 所有数据值均正确的状态

触发器与存储过程的区别

相似：也是SQL语句集

不同：触发器不能用execute语句调用，而是在用户指定Transact-SQL语句自动执行。

运行方式区别：

触发器的分类：

• DDL触发器

  定义：当服务器或者数据库中发生数据定义语言（DDL）时触发器将被调用

  DDL数据定义语言：创建、修改或删除数据库中各种对象，包括表、视图、索引等。

• DML触发器

  定义：当数据库服务器中发生数据操作语言（DML）事件时要执行的操作。

  DML数据操作语言：对已存在数据库表进行元组插入、删除、修改。

DDL触发器

以下操作可以使用DDL触发器

• 要防止对数据库架构进行某些修改

• 希望数据库中发生某种情况以响应数据库架构中的修改

• 要记录数据结构中的更改或者事件

案例：定义一个数据库的DDL触发器，防止在EAS_DB 数据库中表被删除修改。

create trigger TRIG_DDL ---创建一个DDL触发器
for drop_table,drop_view ---删除表或删除视图
on database ---数据库
as
begin
		print ' 无法修改或者删除表，请在之前禁用或删除DDL触发器TRIG_DDL！'
		ROLLBACK TRANSACTION ---采用回滚的办法以达到阻止操作的目的
end

DML触发器

DML触发器的用途：

1. DML触发器可通过数据库中相关表实现级联修改，通过级联引用完整性约束可以更有效的更改

2. DML触发器可以防止恶意或者错误insert、update以及delete操作，并强制执行比check约束定义的限制更为复杂的其他限制

3. DML触发器可以评估数据修改前后表的状态，并根据该差异采取措施

4. 一个表的多个同类DML触发器，允许采用多个不同的操作来响应同一个修改语句。

触发器语法

CREATE [OR REPLACE] TRIGGER 触发器名 触发时间 触发事件
ON 表名
		[FOR EACH ROW]
BEGIN
		pl/sql语句
END

• 触发时间：指明触发器何时执行，该值可取

  • before ：表示在数据库动作之前触发器执行

  • after ：表示在数据库动作之后触发器执行

• 触发事件：指明哪些数据库动作会触发此触发器

  • insert ：数据库插入会触发此触发器

  • update ：数据库修改会触发此触发器

  • delete ：数据库删除会触发此触发器

• 表名：数据库触发器所在的表

• for each row ：对表的每一行触发器执行一次，如果没有这一选项，则只对整个表执行一次。

insert触发器

insert 触发器对目标表执行insert语句时，就会调用触发器。

create trigger tri_insert_stu
after
insert on student_info
for each row
		insert into log_info values ('向学生信息表中插入一行记录');

delete触发器

delete 触发器对目标表执行delete 语句时，就会调用触发器。

update触发器

OLD和NEW的使用

触发器在DDL操作之前或者之后触发，总会对目标表做出一定的改动，有时我们需要知道目标表改变前后的值，这就用到了OLD和NEW。

OLD就是改变之前的值，是UPDATE 之前或者是要被DELETE 或者已经被DELETE 的值。而NEW，则是UPDATE 之后的新值，即将INSERT 或者已经INSERT 的值。

#创建一个update触发器
CREATE TRIGGER T2
AFTER 
UPDATE ON customer
FOR EACH ROW 
#old和new的使用方法：old.columnname/new.columnname（列名）
#将更新前后的值，赋值给两个变量
SELECT OLD.CUST_ID,NEW.CUST_ID INTO @OLD_ID,@NEW_ID;
#触发触发器T2
UPDATE customer SET CUST_ID = '10000' WHERE CUST_NAME = 'AAA';
#查询OLD和NEW
SELECT @OLD_ID,@NEW_ID;

嵌套触发器

如果一个触发器在执行操作时引发了另一个触发器，而这个触发器又接着引发下一个触发器，那么就形成了触发器的嵌套。任何触发器都可以包含影响另一个表的UPDATE 、INSERT 或者DELETE 语句。

嵌套触发器在安装时就被启用

• 可以使用系统存储过程sp_configure 禁用和重新启用嵌套

• 触发器最多可以嵌套32层

• 禁用嵌套：

  EXEC sp_configure ‘nested triggers’,0

• 启用嵌套：

  EXEC sp_configure ‘nested triggers’,1

触发器的执行原理

触发器触发时，创建了DELETED 表和INSERTED 表，只读，不允许修改；触发器执行完成后，自动删除。

1. delete 表

   delete 表存放由执行delete 或者update 语句而要从表中删除的所有行，在执行delete 或update 被删除的行从触发触发器的表中移动到delete表中。

2. insert 表存放由执行insert 或者update 语句而要向表中插入的所有行，在执行insert 或update 新的行同时添加到触发器的表和insert 表中，insert 表中的内容是触发触发器的表新行的副本。

管理触发器

查看触发器

可以使用像sp_helptext 、sp_help 和sp_depends 等系统存储过程来查看触发器等有关信息

exec sp_helptext TRIG_DDL --查看触发器的内容

修改触发器

修改触发器的语法和创建触发器的语法一样，只需要把create 改为alter 即可：

Alter trigger TRIG_DDL ……

删除触发器

drop trigger TRIG_DDL

禁用DML触发器

DISABLE TRIGGER tri_stuinfo_update ON stuInfo

禁用DDL触发器

DISABLE TRIGGER TRIG_DDL ON DATABASE

禁用所有触发器

DISABLE TRIGGER ALL ON ALL SERVER

启用触发器

只要将DISABLE 变为ENABLE 即可

ENABLE TRIGGER tri_stuinfo_update ON stuInfo --启用触发器
ENABLE TRIGGER trig_DDL ON DATABASE --启用数据库触发器
ENABLE TRIGGER ALL ON ALL SERVER --启用以同一作用域定义的所有触发器