SQL Server 创建触发器（trigger）

SQL Server 创建触发器（trigger）

from:https://www.cnblogs.com/Brambling/archive/2017/04/21/6741666.html

如果有id列, 进行更新备份时, 需要取消标识规范

 

 

 

 

触发器简介：

触发器是一种特殊的存储过程，它的执行不是由程序调用，也不是手动执行，而是由事件来触发。触发器是当对某一个表进行操作。例如：update、insert、delete这些操作的时候，系统会自动调用执行该表上对应的触发器。

触发器分类：

1、DML（ 数据操纵语言 Data Manipulation Language）触发器：是指触发器在数据库中发生 DML 事件时将启用。DML事件是指在表或视图中对数据进行的 insert、update、delete 操作的语句。

2、DDL（数据定义语言 Data Definition Language）触发器：是指当服务器或数据库中发生 DDL 事件时将启用。DDL事件是指在表或索引中的 create、alter、drop 操作语句。

3、登陆触发器：是指当用户登录 SQL SERVER 实例建立会话时触发。如果身份验证失败，登录触发器不会触发。

其中 DML 触发器比较常用，根据 DML 触发器触发的方式不同又分为以下两种情况：

after 触发器（之后触发）：其中 after 触发器要求只有执行 insert、update、delete 某一操作之后触发器才会被触发，且只能定义在表上。

instead of 触发器 （之前触发）：instead of 触发器并不执行其定义的操作（insert、update、delete）而仅是执行触发器本身。可以在表或视图上定义 instead of 触发器。

DML 触发器有两个特殊的表：插入表（instered）和删除表（deleted），这两张表是逻辑表。这两个表是建立在数据库服务器的内存中，而且两张表的都是只读的。这两张表的结构和触发器所在的数据表的结构是一样的。当触发器完成工作后，这两张表就会被删除。Inserted 表的数据是插入或是修改后的数据，而 deleted 表的数据是更新前的或是已删除的数据。

AFTER 触发器语法：

 1 CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name   
 2 ON { table }   
 3 [ WITH <dml_trigger_option> [ ,...n ] ]  
 4 { FOR | AFTER }   
 5 { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
 6 AS { sql_statement  [ ; ] [ ,...n ] }  
 7 
 8 <dml_trigger_option> ::=  
 9     [ NATIVE_COMPILATION ]  
10     [ SCHEMABINDING ]  
11     [ EXECUTE AS Clause ]

INSTEAD OF 触发器语法：

 CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name
 ON { table | view }
 [ WITH <dml_trigger_option> [ ,...n ] ]
 { FOR | AFTER | INSTEAD OF }
 { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
 [ WITH APPEND ]
 [ NOT FOR REPLICATION ]
 AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }

 <dml_trigger_option> ::=
 [ ENCRYPTION ]
 [ EXECUTE AS Clause ]

 <method_specifier> ::=
 assembly_name.class_name.method_name

DDL 触发器语法：

 CREATE [ OR ALTER ] 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_trigger_option> ::=
 [ ENCRYPTION ]
 [ EXECUTE AS Clause ]

登陆触发器语法：

 CREATE [ OR ALTER ] TRIGGER trigger_name
 ON ALL SERVER
 [ WITH <logon_trigger_option> [ ,...n ] ]
 { FOR| AFTER } LOGON
 AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier > [ ; ] }

 <logon_trigger_option> ::=
 [ ENCRYPTION ]
 [ EXECUTE AS Clause ]

参数：

CREATE OR ALTER：

创建或者有条件的修改触发器（即要修改的触发器必须已经存在）。

schema_name：
DML触发器所属的模式的名称（即所有者，例如：dbo）。

trigger_name：
是触发器的名称。

table | view：

是执行 DML 触发器的表或视图，有时称为触发器表或触发器视图。指定表格或视图的完全限定名称是可选的。视图只能由 INSTEAD OF 触发器引用。

DATABASE：
将 DDL 触发器的范围应用于当前数据库。如果指定，触发器会在当前数据库中发生 event_type 或 event_group 时触发。

ALL SERVER：

将 DDL 或登录触发器的作用域应用于当前服务器。如果指定，触发器会在当前服务器的任何地方发生 event_type 或 event_group 时触发。

WITH ENCRYPTION：

加密 CREATE TRIGGER 语句的文本。使用 WITH ENCRYPTION 可以防止触发器作为 SQL Server 复制的一部分进行发布。无法为 CLR 触发器指定 WITH ENCRYPTION。

EXECUTE AS：
指定执行触发器的安全上下文。以便能够控制 SQL Server 实例用于验证触发器引用的任何数据库对象的权限的用户帐户。

NATIVE_COMPILATION：
表示触发器是本地编译的。

SCHEMABINDING：
指定触发器引用的表不能被删除或更改。

FOR | AFTER：
AFTER 指定仅在触发 SQL 语句中指定的所有操作成功执行时触发 DML 触发器。所有引用级联操作和约束检查在此触发器触发之前也必须成功。当 FOR 是指定的唯一关键字时，AFTER 是默认值。视图无法定义AFTER触发器。

INSTEAD OF：
指定执行 DML 触发器而不是触发 SQL 语句，因此覆盖触发语句的操作。无法为 DDL 或登录触发器指定 INSTEAD OF。

对于 INSTEAD OF 触发器，在具有指定级联动作 ON DELETE 的引用关系的表上不允许使用 DELETE 选项。类似地，在具有指定级联动作 ON UPDATE 的引用关系的表上，不允许 UPDATE 选项。

{[DELETE] [，] [INSERT] [，] [UPDATE]} ：
指定在针对此表或视图进行尝试时激活 DML 触发器的数据修改语句。必须至少指定一个选项。在触发器定义中允许以任何顺序对这些选项进行任意组合。

event_type：
是执行后导致 DDL 触发器触发的 Transact-SQL 语言事件的名称。

event_group：
是 Transact-SQL 语言事件的预定义分组的名称。属于任何 Transact-SQL 语言事件执行后的 DDL 触发器触发 event_group。

sql_statement：
是触发条件和动作。触发条件指定附加条件，以确定尝试的 DML，DDL 或登录事件是否导致执行触发器操作。

<method_specifier>：

对于 CLR 触发器，指定要与触发器绑定的程序集的方法。该方法不得不引用任何参数并返回 void。class_name 必须是有效的 SQL Server 标识符，并且必须作为具有程序集可见性的程序集中的类存在。

 

以下是DML触发器的使用，先看看示例数据：

insert 触发器：

 if(OBJECT_ID('trigger_Stu_Insert') is not null) -- 判断名为 trigger_Stu_Insert 的触发器是否存在
2 drop trigger trigger_Stu_Insert -- 删除触发器
3 go
4 create trigger trigger_Stu_Insert
5 on Student -- 指定创建触发器的表
6 for insert -- insert 触发器，也可以写为 after insert
7 as
8
9 declare @C_Id int
10 declare @S_Id int
11
12 select @C_Id=C_Id from Course where C_Name='SQL' -- 获取课程为 SQL 的ID
13 select @S_Id=S_Id from inserted --插入一条学生的数据，那么这条数据就存在 inserted 这个表中
14
15 select @C_Id
16 select @S_Id
17
18 select * from inserted
19
20 update Student set C_S_Id=@C_Id where S_Id=@S_Id
21 go
22
23 insert into Student(S_StuNo,S_Name,S_Sex,S_Height,S_BirthDate)
24 values('016','大熊','男','210','2017-01-01')
25
26 select * from Student
27 select * from Course

这个例子是：当 Student 表新增一条数据时，修改这条数据的课程ID。

delete 触发器：

 if(OBJECT_ID('trigger_Stu_Delete') is not null) -- 判断名为 trigger_Stu_Delete 的触发器是否存在
2 drop trigger trigger_Stu_Delete -- 删除触发器
3 go
4 create trigger trigger_Stu_Delete
5 on Student -- 指定创建触发器的表
6 for delete -- delete 触发器，也可以写为 after delete
7 as
8
9 declare @C_S_Id int
10
11 select @C_S_Id=C_S_Id from deleted --删除的学生的数据就存在 deleted 这个表中
12
13 select @C_S_Id
14
15 select * from deleted
16
17 delete from Course where C_Id=@C_S_Id -- 删除具有删除的学生的课程ID的课程
18 go
19
20 delete from Student where C_S_Id='1'
21
22 select * from Student
23 select * from Course

这个例子是：删除指定课程ID的学生时，并删除指定课程ID的课程。

update 触发器：

if(OBJECT_ID('trigger_Cou_Update') is not null) -- 判断名为 trigger_Cou_Update 的触发器是否存在
drop trigger trigger_Cou_Update -- 删除触发器
go
create trigger trigger_Cou_Update
on Course -- 指定创建触发器的表
for update -- update 触发器，也可以写为 after update
as

declare @C_Id int
select @C_Id=C_Id from deleted
select * from deleted -- 修改前的数据就存在 deleted 这个表中
select * from inserted -- 修改后的数据就存在 inserted 这个表中
update Student set C_S_Id=@C_Id where C_S_Id is null
go

update Course set C_Name='C#' where C_Id='4'

select * from Student
select * from Course

这个例子是：修改课程名称时，把课程ID为空（null）的学生的课程ID默认为修改的课程ID。

禁止修改学生学号触发器，触发器进行数据回滚：

if(OBJECT_ID('trigger_Stu_Update') is not null) -- 判断名为 trigger_Stu_Update 的触发器是否存在
drop trigger trigger_Stu_Update -- 删除触发器
go
create trigger trigger_Stu_Update
on Student -- 指定创建触发器的表
for update -- update 触发器，也可以写为 after update
as
begin try
if(UPDATE(S_StuNo)) -- 列级触发器：判断是否更新了学生学号（学号不允许更改）
begin
raiserror(66666,16,1)
end
end try
begin catch
select * from deleted -- 修改前的数据就存在 deleted 这个表中
select * from inserted -- 修改后的数据就存在 inserted 这个表中
rollback tran;
end catch
go
update Student set S_StuNo='006' where S_Id='20'
select * from Student

after 触发器可以指定多个操作都可以触发该触发器。只需要在 for/after 后面添加逗号和触发器的类型，例如：

1 for update,insert,delete 
2 
3 after update,insert,delete 

instead of 触发器：

这个触发器就好玩了，下面先看看数据。

 if(OBJECT_ID('trigger_Stu_InsteadOf') is not null) -- 判断名为 trigger_Stu_InsteadOf 的触发器是否存在
2 drop trigger trigger_Stu_InsteadOf -- 删除触发器
3 go
4 create trigger trigger_Stu_InsteadOf
5 on Student -- 指定创建触发器的表
6 instead of update,insert,delete -- instead of 触发器
7 as
8 select * from deleted -- 修改前的数据就存在 deleted 这个表中
9 select * from inserted -- 修改后的数据就存在 inserted 这个表中
10 go
11
12 update Student set S_StuNo='006' where S_Id='20'
13
14 insert into Student([S_StuNo],[S_Name],[S_Sex],[S_Height],[S_BirthDate])
15 values('017','清红','女','180','2017-01-01')
16
17 delete from Student where C_S_Id='5'
18
19 select * from Student

执行上面的语句之后，咦，数据怎么一点变化都没有？看看上面的介绍。instead of 触发器是之前触发。

instead of 触发器并不执行其定义的操作（insert、update、delete）而仅是执行触发器本身，并且会覆盖触发语句的操作，即 after 触发器 T-SQL 语句的操作，很明显我们上面定义的表 Student 的 after 触发器也没有效果了，现在理解了这句话了吧。

修改触发器：

 alter trigger trigger_Stu_InsteadOf -- 修改触发器
2 on Student -- 指定创建触发器的表
3 instead of update,insert,delete -- instead of 触发器
4 as
5 declare @Count1 int
6 declare @Count2 int
7
8 select @Count1=COUNT(1) from deleted
9 select @Count2=COUNT(1) from inserted
10
11 if(@Count1>0 and @Count2>0)
12 begin
13 select 'update操作'
14 end
15 else if(@Count1>0)
16 begin
17 select 'delete操作'
18 end
19 else if(@Count2>0)
20 begin
21 select 'insert操作'
22 end
23 go
24
25 update Student set S_StuNo='006' where S_Id='20'
26
27 insert into Student([S_StuNo],[S_Name],[S_Sex],[S_Height],[S_BirthDate])
28 values('017','清红','女','180','2017-01-01')
29
30 delete from Student where C_S_Id='5'
31
32 select * from Student

启用/禁用触发器：

1 --禁用触发器
2 disable trigger trigger_Stu_InsteadOf on Student;    -- trigger_Stu_InsteadOf 触发器名称
3 --启用触发器
4 enable trigger trigger_Stu_InsteadOf on Student;    -- trigger_Stu_InsteadOf 触发器名称

查询已存在的触发器：

1 -- 查询已存在的触发器
2 select * from sys.triggers;
3 select * from sys.objects where type = 'TR';
4 select * from sysobjects where xtype='TR'

 -- sys.trigger_events 触发器事件对象视图
2 select * from sys.trigger_events
3
4 -- 查看触发器触发事件对象
5 select a.type_desc,b.* from sys.trigger_events a
6 inner join sys.triggers b on a.object_id = b.object_id
7 where b.name = 'trigger_Stu_Insert';
8
9 -- 查询创建触发器的 T-SQL 文本
10 exec sp_helptext 'trigger_Stu_Insert'