SQL Server 创建触发器(trigger)

触发器简介:

触发器是同样种植特殊的囤积过程,它的推行不是出于程序调用,也无是手动执行,而是由事件来点。触发器是当对有一个表展开操作。例如: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
表的数是创新前的或者已抹的数码。

图片 1

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.class_name.method_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
标识符,并且要作为有次集可见性的次第集中之好像存在。

 

以下是DML触发器的采用,先看看示例数据:

图片 2

 

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

图片 3

图片 4

其一事例是:当 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

图片 5

图片 6

这事例是:删除指定课程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

图片 7

图片 8

夫事例是:修改课程名称时,把课程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

图片 9

after 触发器可以指定多个操作都足以触发发该触发器。只待以 for/after
后面加加逗号和触发器的路,例如:

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

instead of 触发器:

这个触发器就哼游戏了,下面先瞧数据。

图片 10

 1 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

图片 11

实施方的口舌之后,咦,数据怎么一点变都无?看看上面的介绍。instead
of 触发器是事先接触。

instead of
触发器并无履该定义之操作(insert、update、delete)而独是执行触发器本身,并且会蒙触发语词的操作,即
after 触发器 T-SQL 语句之操作,很明显我们地方定义的表 Student 的 after
触发器也远非效应了,现在清楚了立即词话了吧。

修改触发器:

 1 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

图片 12

启用/禁用触发器:

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'

 1 -- 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'

 

参考:

http://www.cnblogs.com/hoojo/archive/2011/07/20/2111316.html

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql\#remarks-dml-triggers

相关文章