SQLServer创建触发器（trigger）
from:wwwblogs/Brambling/archive/2017/04/21/6741666.html
触发器简介：
触发器是⼀种特殊的存储过程，它的执⾏不是由程序调⽤，也不是⼿动执⾏，⽽是由事件来触发。触发器是当对某⼀个表进⾏操作。例如：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 触发器语法：
1 CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name
2 ON { table | view }
3 [ WITH <dml_trigger_option> [ ,...n ] ]
4 { FOR | AFTER | INSTEAD OF }
5 { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
6 [ WITH APPEND ]
7 [ NOT FOR REPLICATION ]
8 AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }
9
10 <dml_trigger_option> ::=
11 [ ENCRYPTION ]
12 [ EXECUTE AS Clause ]
13
14 <method_specifier> ::=
15 assembly_name.hod_name
DDL 触发器语法：
1 CREATE [ OR ALTER ] TRIGGER trigger_name
2 ON { ALL SERVER | DATABASE }
3 [ WITH <ddl_trigger_option> [ ,...n ] ]
4 { FOR | AFTER } { event_type | event_group } [ ,...n ]
5 AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier > [ ; ] }
6
7 <ddl_trigger_option> ::=
8 [ ENCRYPTION ]
9 [ EXECUTE AS Clause ]
登陆触发器语法：
1 CREATE [ OR ALTER ] TRIGGER trigger_name
2 ON ALL SERVER
3 [ WITH <logon_trigger_option> [ ,...n ] ]
4 { FOR| AFTER } LOGON
5 AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier > [ ; ] }
6
7 <logon_trigger_option> ::=
8 [ ENCRYPTION ]
9 [ 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 标识符，并且必须作为具有程序集可见性的程序集中的类存在。
sql触发器的使用以下是DML触发器的使⽤，先看看⽰例数据：
insert 触发器：
1 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 触发器：
1 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 触发器：
1 if(OBJECT_ID('trigger_Cou_Update') is not null) -- 判断名为 trigger_Cou_Update 的触发器是否存在
2 drop trigger trigger_Cou_Update -- 删除触发器
3 go
4 create trigger trigger_Cou_Update
5 on Course -- 指定创建触发器的表
6 for update -- update 触发器，也可以写为 after update
7 as
8
9 declare @C_Id int
10
11 select @C_Id=C_Id from deleted
12
13 select * from deleted -- 修改前的数据就存在 deleted 这个表中
14
15 select * from inserted -- 修改后的数据就存在 inserted 这个表中
16
17 update Student set C_S_Id=@C_Id where C_S_Id is null
18 go
19
20 update Course set C_Name='C#' where C_Id='4'
21
22 select * from Student
23 select * from Course
这个例⼦是：修改课程名称时，把课程ID为空（null）的学⽣的课程ID默认为修改的课程ID。禁⽌修改学⽣学号触发器，触发器进⾏数据回滚：
1 if(OBJECT_ID('trigger_Stu_Update') is not null) -- 判断名为 trigger_Stu_Update 的触发器是否存在
2 drop trigger trigger_Stu_Update -- 删除触发器
3 go
4 create trigger trigger_Stu_Update
5 on Student -- 指定创建触发器的表
6 for update -- update 触发器，也可以写为 after update
7 as
8 begin try
9 if(UPDATE(S_StuNo)) -- 列级触发器：判断是否更新了学⽣学号（学号不允许更改）
10 begin
11 raiserror(66666,16,1)
12 end
13 end try
14 begin catch
15 select * from deleted -- 修改前的数据就存在 deleted 这个表中
16 select * from inserted -- 修改后的数据就存在 inserted 这个表中
17 rollback tran;
18 end catch
19 go
20
21 update Student set S_StuNo='006' where S_Id='20'
22
23 select * from Student
发表评论