SQL Server 触发器

概述

触发器为特殊类型的存储过程，可在执行语言事件时自动生效。SQL Server 包括三种常规类型的触发器：DML 触发器、DDL 触发器和登录触发器。

当服务器或数据库中发生数据定义语言 (DDL) 事件时将调用 DDL 触发器。登录触发器将为响应 LOGON 事件而激发存储过程。与 SQL Server 实例建立用户会话时将引发此事件。

当数据库中发生数据操作语言 (DML) 事件时将调用 DML 触发器。DML 事件包括在指定表或视图中修改数据的 INSERT 语句、UPDATE 语句或 DELETE 语句。DML 触发器可以查询其他表，还可以包含复杂的 Transact-SQL 语句。将触发器和触发它的语句作为可在触发器内回滚的单个事务对待。如果检测到错误（例如，磁盘空间不足），则整个事务即自动回滚。

DML触发器

DML触发器有两种：AFTER，INSTEAD OF触发器，同时DML 触发器使用 deleted 和 inserted 逻辑（概念）表。它们在结构上类似于定义了触发器的表，即对其尝试执行了用户操作的表。在 deleted 和 inserted 表保存了可能会被用户更改的行的旧值或新值。

对于INSERT 操作，inserted保留新增的记录，deleted无记录
对于DELETE 操作，inserted无记录，deleted保留被删除的记录
对于UPDATE操作，inserted保留修改后的记录，deleted保留修改前的记录

一.语法

CREATE TRIGGER [ schema_name . ]trigger_name 
ON { table | view } 
 [ WITH <dml_trigger_option> [ ,...n ] ] 
{ FOR | AFTER | INSTEAD OF } 
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } 
  AS { sql_statement  [ ; ] [ ,...n ] [ ; ] > }

二.创建表

CREATE TABLE Class
(Cno INT PRIMARY KEY,
Cname nvarchar(20) not null)
go
CREATE TABLE Student
(SNO INT PRIMARY KEY IDENTITY(1,1),
Sname CHAR(10) not null,
Age int not null,
Sex char(2) not null,
Cno int NOT NULL
)
ALTER TABLE Student ADD CONSTRAINT FK_SNO_Cno FOREIGN KEY (Cno) REFERENCES Class(Cno)
go

AFTER触发器

AFTER 指定 DML 触发器仅在触发 SQL 语句中指定的所有操作都已成功执行时才被触发。所有的引用级联操作和约束检查也必须在激发此触发器之前成功完成。

如果仅指定 FOR 关键字，则 AFTER 为默认值。

不能对视图定义 AFTER 触发器

insert触发

当向Class表中插入一条数据时，获取插入的cno，同时向Student表中插入一条数据

IF OBJECT_ID('TR_Class_insert','TR') IS NOT NULL
DROP TRIGGER TR_Class_insert
G0
CREATE TRIGGER TR_Class_insert on Class
AFTER INSERT
AS
BEGIN
  DECLARE @Cno INT
  SELECT @Cno=Cno FROM inserted----获取插入的数据CNO
  INSERT INTO Student(Sname,Age,Sex,Cno)
  VALUES('李明',20,'男',@Cno)
END 

go
INSERT INTO Class
SELECT 101,'一班'

SELECT * FROM Class
SELECT * FROM Student

UPDATE触发

获取修改的Age值，如果Age为负数则执行回滚操作，否则输出修改前后的Age值

IF OBJECT_ID('TR_Student_update','TR') IS NOT NULL
DROP TRIGGER TR_Student_update
GO
CREATE TRIGGER TR_Student_update on Student
AFTER UPDATE
AS
BEGIN
   DECLARE @Age_old int,@Age_new int
   SELECT @Age_old=Age from deleted ----获取修改前的
   SELECT @Age_new=Age FROM inserted----获取更改后的数据
   if @Age_new<0
   begin
   print '年龄不能为负数'
   rollback;
   end
   else 
   BEGIN
   print @Age_old
   print @Age_new
   END
END

go
update Student
set Age=-20
where SNO=1

SELECT * FROM Class
SELECT * FROM Student

update Student
set Age=25
where SNO=1


SELECT * FROM Class
SELECT * FROM Student

delete触发

获取被删除的数据，返回错误提示，该步骤正好验证了“所有的引用级联操作和约束检查也必须在激发此触发器之前成功完成”，该步骤不会返回制定的错误提示，因为被删除的数据作用于外键约束，所以先于触发器操作执行外键约束，返回约束错误提示，并执行回滚.

IF OBJECT_ID('TR_Class_delete','TR') IS NOT NULL
DROP TRIGGER TR_Class_delete
GO
CREATE TRIGGER TR_Class_delete on Class
AFTER DELETE
AS
BEGIN
  DECLARE @Cno int
  SELECT @Cno=Cno from DELETED---获取被删除的记录
  IF @Cno>0begin
  RAISERROR ('数据不能被删除，被用于外键约束', 16, 10);
  rollback----执行回滚操作
  end

END


SELECT * FROM Class
SELECT * FROM Student

DELETE FROM Class
where CNO=101

SELECT * FROM Class
SELECT * FROM Student

对Student表建立外键约束，用于级联操作 ON DELETE，对于表的级联删除更新操作这里就不讲述了
删除之前创建的外键约束,并创建具有级联更新删除操作的外键约束

alter table student
drop constraint FK_SNO_Cno


ALTER TABLE Student
ADD CONSTRAINT FK_SNO_Cno FOREIGN KEY (Cno) REFERENCES Class (Cno)
ON DELETE CASCADE ON UPDATE CASCADE

再执行删除语句，返回制定错误提示“数据不能被删除，被用于外键约束”并执行回滚操作

DELETE FROM Class
where CNO=101

SELECT * FROM Class
SELECT * FROM Student

INSTEAD OF触发器

指定执行 DML 触发器而不是触发 SQL 语句，因此，其优先级高于触发语句的操作。不能为 DDL 或登录触发器指定 INSTEAD OF。

对于表或视图，每个 INSERT、UPDATE 或 DELETE 语句最多可定义一个 INSTEAD OF 触发器。但是，可以为具有自己的 INSTEAD OF 触发器的多个视图定义视图。

INSTEAD OF 触发器不可以用于使用 WITH CHECK OPTION 的可更新视图。如果将 INSTEAD OF 触发器添加到指定了 WITH CHECK OPTION 的可更新视图中，则 SQL Server 将引发错误。用户须用 ALTER VIEW 删除该选项后才能定义 INSTEAD OF 触发器

对于 INSTEAD OF 触发器，不允许对具有指定级联操作 ON DELETE 的引用关系的表使用 DELETE 选项。同样，也不允许对具有指定级联操作 ON UPDATE 的引用关系的表使用 UPDATE 选项

Insert 触发

-------insert 触发
----删除已有的instead of触发器
declare @name nvarchar(100)
select @name=name from sys.triggers where  object_name(parent_id)='student' and is_instead_of_trigger=1
set @name='drop trigger '+@name
exec (@name)

IF OBJECT_ID('TR_Student_instead_insert','TR') IS NOT NULL
DROP TRIGGER TR_Student_instead_insert
GO
CREATE TRIGGER TR_Student_instead_insert on Student
INSTEAD OF insert
AS
BEGIN


 SELECT * into T_back from inserted ----获取即将插入的数据
 
END


select * from Student
select * from Class

INSERT INTO Student(Sname,Age,Sex,Cno)
values('张三',23,'男',102)

select * from T_back

delete触发

创建触发器失败，因为之前创建外键约束时添加了on delete cascade

IF OBJECT_ID('TR_Student_instead_delete','TR') IS NOT NULL
DROP TRIGGER TR_Student_instead_delete
GO
CREATE TRIGGER TR_Student_instead_delete on Student
INSTEAD OF DELETE
AS
BEGIN
  DECLARE @Cno int
  SELECT @Cno=Cno from DELETED---获取被删除的记录
  IF EXISTS (SELECT * FROM Class where Cno=@cno) 
  begin
  rollback----执行回滚操作
  RAISERROR ('数据不能被删除，被用于外键约束1', 16, 10);
  end

END

消息 2113，级别 16，状态 1，过程 TR_Student_instead_delete，第 10 行
因为表 'Student' 的 FOREIGN KEY 使用级联 DELETE 或 UPDATE，所以无法对该表 创建 INSTEAD OF DELETE 或 INSTEAD OF UPDATE TRIGGER 'TR_Student_instead_delete'。

重建外键约束，删除级联

alter table student
drop constraint FK_SNO_Cno

ALTER TABLE Student
ADD CONSTRAINT FK_SNO_Cno FOREIGN KEY (Cno) REFERENCES Class (Cno)

UPDATE触发

-----同一张表中只能定义一个instead of 触发器，删除表之前创建的instead of 触发

declare @name nvarchar(100)
select @name=name from sys.triggers where  object_name(parent_id)='student' and is_instead_of_trigger=1
set @name='drop trigger '+@name
exec (@name)

IF OBJECT_ID('TR_Student_instead_update','TR') IS NOT NULL
DROP TRIGGER TR_Student_instead_update
GO
CREATE TRIGGER TR_Student_instead_update on Student
INSTEAD OF update
AS
BEGIN
  DECLARE @Age_del int ,@Age_up int
  SELECT @Age_del=Age from DELETED---获取被更改的记录
  SELECT @Age_up=Age from Inserted
  begin
  print @Age_del
  print @Age_up
  select * from Student ----查询数据是否被更改
  end

END

----查询更新前的表数据
select * from student 

SNO    Sname    Age    Sex    Cno
13    李明          22    男    101

update Student
set age=-2
where CNO=101


----对于前面定义的after触发器age不能为负数也不会执行，instead of 触发器高于执行语句，高于after 触发

SNO    Sname    Age    Sex    Cno
13    李明          22    男    101

select * from student 

SNO    Sname    Age    Sex    Cno
13    李明          22    男    101


(1 行受影响)
22
-2

(1 行受影响)

(1 行受影响)

当表上面定义了instead of 触发器，指定执行 DML 触发器而不是触发 SQL 语句，因此，其优先级高于触发语句的操作，而且也不会执行表上面定义的after触发器

创建带字段判断的触发器，根据对特定列的 UPDATE 或 INSERT 修改来执行某些操作

------创建字段更新判断的update触发器
ALTER TABLE Class
ADD Address nvarchar(50)

IF OBJECT_ID('TR_Class_Update','TR') IS NOT NULL
DROP TRIGGER TR_Class_Update
GO
CREATE TRIGGER TR_Class_Update on Class
AFTER UPDATE
AS
BEGIN
  IF UPDATE(Cname) or UPDATE(Address)
  BEGIN
  RAISERROR ('数据不能被修改', 16, 10)
  ROLLBACK
  END
END

SELECT * FROM Class

UPDATE Class
set Address='5栋101'
where Cno=101

SELECT * FROM Class

注意：

After触发器只能用于数据表中，Instead of触发器可以用于数据表和视图上，且两种触发器都不能建立在临时表上。
一个数据表可以建多个触发器，但是一个触发器只能对应一张表。
在同一个数据表中，对应每一个操作（insert,delete,update）可以建多个After触发器，但是只能建一个Instead of触发器。
Instead of触发器不会执行原本的SQL语句，只执行触发器中定义的操作。
如果针对某个操作既定义了After触发器又定义了Instead of触发器，那么Instead of触发器一定会触发，而After触发器不一定会触发。
Truncate table操作不会激活delete类型的触发，因为它不进行删除日志写入操作。
writetext语句不能触发insert和update类型触发器。

DLL触发器

像常规触发器一样，DDL 触发器将激发存储过程以响应事件。但与 DML 触发器不同的是，它们不会为响应针对表或视图的 UPDATE、INSERT 或 DELETE 语句而激发。相反，它们将为了响应各种数据定义语言 (DDL)事件而激发。这些事件主要与以关键字 CREATE、ALTER 和 DROP 开头的 Transact-SQL 语句对应。执行 DDL 式操作的系统存储过程也可以激发 DDL 触发器。

DLL触发器有针对服务器级别的触发事件，也有针对数据库级别的触发事件。

语法：

CREATE TRIGGER trigger_name 
ON { ALL SERVER | DATABASE } 
[ WITH <ddl_trigger_option> [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

注意：DDL触发器中不能指定instead of触发器

DDL触发器在服务器级别下可用的事件有：

DDL触发器在数据库级别下可用的事件有：

----阻止删除和修改任意表

CREATE TRIGGER TR_safety ---触发器名
ON DATABASE ---不用修改
FOR DROP_TABLE, ALTER_TABLE 
AS 
BEGIN
   PRINT 'You must disable Trigger "safety" to drop or alter tables!' 
   ROLLBACK ;
END

ALTER TRIGGER TR_safety_On_Database
ON ALL SERVER
FOR DROP_DATABASE,ALTER_DATABASE
AS 
BEGIN
   PRINT 'You must disable Trigger "TR_safety_On_Database" to drop database or alter database!' 
   ROLLBACK ;
END

注意：在当前数据库下执行

总结

虽然触发器功能强大，轻松可靠地实现许多复杂的功能，同时过多触发器会造成数据库及应用程序的维护困难，同时对触发器过分的依赖，势必影响数据库的结构，同时增加了维护的复杂程序.

附加一张所有触发事件图：

备注：

作者：pursuer.chen

博客：http://www.cnblogs.com/chenmh

本站点所有随笔都是原创，欢迎大家转载；但转载时必须注明文章来源，且在文章开头明显处给明链接，否则保留追究责任的权利。

《欢迎交流讨论》

posted @ 2014-05-14 13:43 pursuer.chen 阅读(5879) 评论(6) 编辑收藏举报

刷新页面返回顶部

pursuer.chen

LIVE AND LEARN

SQL Server 触发器

公告