【SQL server速成之路】触发器

??个人主页：
往期专栏：【速成之路】jQuery

---

??专栏：【速成之路】SQL server
??往期回顾：
【SQL server速成之路】数据库基础
【SQL server速成之路】数据库和表（一）
【SQL server速成之路】数据库和表（二）
【SQL server速成之路】数据库的查询
【SQL server速成之路】数据库的视图和游标
【SQL server速成之路】T-SQL语言（一）
【SQL server速成之路】T-SQL语言（二）
【SQL server速成之路】函数
【SQL server速成之路】索引与数据完整性

触发器

• • 利用SQL命令创建触发器
  • 触发器的修改
  • 触发器的删除
  • 界面方式操作触发器

触发器不需要调用，当对一个表的特别事件出现时，它就会被激活。触发器是一类特殊的存储过程，用于保护表中的数据，当有操作影响到触发器保护的数据时，触发器将自动执行。
在SQL Server 2012中，按照触发事件的不同可以将触发器分为两大类：DML触发器和DDL触发器。

（1）DML触发器。当数据库中发生数据操纵语言（DML）事件时将调用DML触发器。一般情况下，DML事件包括对表或视图的INSERT语句、UPDATE语句和DELETE语句，因而DML触发器也可分为三种类型：INSERT、UPDATE和DELETE。

（2）DDL触发器。DDL触发器也是由相应的事件触发，但DDL触发器触发的事件是数据定义语句（DDL）语句。这些语句主要是以CREATE、ALTER、DROP等关键字开头的语句。DDL触发器的主要作用是执行管理操作，例如审核系统、控制数据库的操作等。

利用SQL命令创建触发器

1．创建DML触发器

CREATE TRIGGER [ <架构名>. ]<触发器名> 
	ON <表名或视图名> 									/*指定操作对象*/
	[ WITH  ENCRYPTION ]							      /*说明是否采用加密方式*/
	{ FOR |AFTER | INSTEAD OF } 
	{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }        	/*指定激活触发器的动作*/
	[ NOT FOR REPLICATION ] 							/*说明该触发器不用于复制*/
AS  sql_statement [ ; ]
1
2
3
4
5
6
7

说明：
（1）触发器激活的时机

① AFTER：用于说明触发器在指定操作都成功执行后触发，如AFTER INSERT表示向表中插入数据时激活触发器。
② INSTEAD OF：指定用DML触发器中的操作代替触发语句的操作。

（2）激活触发器的语句类型

{[DELETE] [,] [INSERT] [,] [UPDATE]}指定激活触发器的语句的类型，必须至少指定一个选项。在触发器定义中允许使用上述选项的任意顺序组合。

（3）sql_statement触发器

执行T-SQL语句，可以有一条或多条语句，用于指定DML触发器触发后将要执行的动作。

（4）触发器中使用的特殊表

执行触发器时，系统创建了两个特殊的临时表inserted表和deleted表。

（5）创建DML触发器主要有以下几点说明

① CREATE TRIGGER 语句必须是批处理中的第一条语句，并且只能应用到一个表中。
② DML触发器只能在当前的数据库中创建，但可以引用当前数据库的外部对象。
③ 创建DML触发器的权限默认分配给表的所有者。
④ 在同一CREATE TRIGGER语句中，可以为多种操作（如INSERT和UPDATE）定义相同的触发器操作。
⑤ 不能对临时表或系统表创建DML触发器。
⑥ 对于含有DELETE或UPDATE操作定义的外键表，不能使用INSTEAD OF DELETE和INSTEAD OF UPDATE触发器。
⑦ TRUNCATE TABLE语句虽然能够删除表中记录，但它不会触发DELETE触发器。
⑧ 在触发器内可以指定任意的SET语句，所选择的SET选项在触发器执行期间有效，并在触发器执行完后恢复到以前的设置。
⑨ DML触发器最大的用途是返回行级数据的完整性，而不是返回结果。所以应当尽量避免返回任何结果集。
⑩ DML触发器中不能包含以下语句：ALTER DATABASE、CREATE DATABASE、DROP DATABASE、RESTORE DATABASE等。

【例1】 对于xsbook数据库，如果在xs表中添加或更改数据，则向客户端显示一条“TRIGGER IS WORKING”的信息。

/*使用带有提示消息的触发器*/
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'reminder' AND type = 'TR')
	DROP TRIGGER reminder
GO
CREATE TRIGGER reminder ON xs
	FOR INSERT, UPDATE 
	AS
	BEGIN
		DECLARE @str char(50)
		SET @str='TRIGGER IS WORKING'
		PRINT @str
	END
GO
1
2
3
4
5
6
7
8
9
10
11
12
13

向xs表中插入一行数据：

INSERT INTO xs VALUES('141101','吴越',1,'1996-06-20', ,'英语',0,NULL)
1

执行结果如图所示：

【例2】 在xsbook数据库的jy表上创建一个UPDATE触发器，若对借书证号列和图书的ISBN列修改，则给出提示信息，并取消修改操作

CREATE TRIGGER update_trigger1
ON jy
FOR UPDATE 
AS
/*检查借书证号列或ISBN列是否被修改，如果有某些列被修改了，则取消修改操作*/
	IF UPDATE(借书证号) OR UPDATE(ISBN)
	BEGIN
		PRINT '违背数据的一致性'
		ROLLBACK TRANSACTION
	END
1
2
3
4
5
6
7
8
9
10

注：update函数用于测试在指定列上进行的insert或update操作，该列不能为计算列，若要测试多个列，则用and/or逻辑运算符连接。

下面介绍INSTEAD OF触发器的设计。AFTER触发器是在触发语句执行后触发的，与AFTER触发器不同的是，INSTEAD OF触发器触发时只执行触发器内部的SQL语句，而不执行激活该触发器的SQL语句。一个表或视图中只能有一个INSTEAD OF触发器。

【例3】 创建表table1，值包含一列a，在表中创建INSTEAD OF INSERT触发器，当向表中插入记录时显示相应消息。

CREATE TABLE table1 (a int)
GO
CREATE TRIGGER table1_insert
		ON table1 INSTEAD OF INSERT
	AS
		PRINT 'INSTEAD OF TRIGGER IS WORKING'
1
2
3
4
5
6

向表中插入一行数据：

INSERT INTO table1 VALUES(10)
1

执行结果如图所示。

【例4】 在xsbook数据库中创建表、视图和触发器，以说明INSTEAD OF INSERT触发器的使用。
如果对引用View2视图的INSERT语句的每一列都指定值，例如：

INSERT INTO View2 (BookKey ,BookName ,Color, ComputedCol ,Pages)
	VALUES (4, '计算机辅助设计', '红色', '绿色',100)
1
2

查看INSERT 语句的执行结果：

SELECT * FROM View2
CREATE TRIGGER trig ON View2 INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO books
    SELECT BookName, Color, Pages from inserted
END
1
2
3
4
5
6
7

结果如图所示。

2．创建DDL触发器
语法格式：

CREATE TRIGGER <触发器名称> 
	ON { ALL SERVER | DATABASE } 
	[ WITH ENCRYPTION ]
	{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS	 sql_statement  [ ; ] [ ...n ]
1
2
3
4
5

说明：

• ALL SERVER | DATABASE：ALL SERVER关键字是指将当前DDL触发器的作用域应用于当前服务器， DATABASE指将当前DDL触发器的作用域应用于当前数据库。
• event_type：执行之后将导致触发DDL触发器的T-SQL语句事件的名称。
• event_group：预定义的T-SQL语句事件分组的名称。

【例5】 创建xsbook数据库作用域的DDL触发器，当删除一个表时，提示禁止该操作，然后回滚删除表的操作。

CREATE TRIGGER safety
	ON DATABASE
	AFTER DROP_TABLE
	AS
		PRINT '不能删除该表'
		ROLLBACK TRANSACTION
1
2
3
4
5
6

尝试删除表table1：

DROP TABLE table1
1

执行结果如图所示：

【例6】 创建服务器作用域的DDL触发器，当删除一个数据库时，提示禁止该操作并回滚删除数据库的操作。

CREATE TRIGGER safety_server
	ON ALL SERVER
	AFTER DROP_DATABASE
	AS
		PRINT '不能删除该数据库'
		ROLLBACK TRANSACTION
1
2
3
4
5
6

触发器的修改

要修改触发器执行的操作，可以使用ALTER TRIGGER语句。

（1）修改DML触发器的语法格式：

ALTER TRIGGER <触发器名> 
	ON <表名或视图名> 
	[ WITH ENCRYPTION ]
	( FOR | AFTER | INSTEAD OF ) 
		{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] } 
	[ NOT FOR REPLICATION ] 
	AS  sql_statement [ ; ] [ ...n ]
1
2
3
4
5
6
7

（2）修改DDL触发器的语法格式：

ALTER TRIGGER <触发器名>
	ON { DATABASE | ALL SERVER } 
	[ WITH ENCRYPTION ]
	{ FOR | AFTER } { event_type [ ,...n ] | event_group } 
	AS   sql_statement [ ; ]
1
2
3
4
5

【例7】 修改xsbook数据库中在xs表上定义的触发器reminder。

ALTER TRIGGER reminder ON xs
	FOR UPDATE 
	AS PRINT '执行的操作是修改'
1
2
3

触发器的删除

触发器本身是存在表中的，因此，当表被删除时，表中的触发器也将一起被删除。删除触发器使用DROP TRIGGER语句。

语法格式：

DROP TRIGGER <触发器名> [ ,...n ] [ ; ]		/*删除DML触发器*/
DROP TRIGGER <触发器名> [ ,...n ] ON { DATABASE | ALL SERVER }[ ; ] /*删除DDL触发器*/
1
2

【例8】 删除触发器reminder。

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'reminder' AND type = 'TR')
	DROP TRIGGER reminder
1
2

【例9】 删除DDL触发器safety。

DROP TRIGGER safety ON DATABASE
1

界面方式操作触发器

1．创建触发器

（1）通过界面方式只能创建DML触发器。
以在表xs上创建触发器为例，利用“对象资源管理器”创建DML触发器步骤如下：在“对象资源管理器”中展开“数据库”→“xsbook”→“表”→“dbo.xs”→选择其中的“触发器”目录，在该目录下可以看到之前已经创建的xs表的触发器。右击“触发器”，在弹出的快捷菜单中选择“新建触发器”菜单项。在打开的“触发器脚本编辑”窗口输入相应的创建触发器的命令。

（2）查看DDL触发器。
DDL触发器不可以使用界面方式创建，DDL触发器分为数据库触发器和服务器触发器，展开“数据库”→“xsbook”→“可编程性”→“数据库触发器”就可以查看到有哪些数据库触发器。展开“数据库”→“服务器对象”→“触发器”就可以查看到有哪些服务器触发器。

2．修改触发器

DML触发器能够使用界面方式修改，DDL触发器则不可以。进入“对象资源管理器”，修改触发器的步骤与创建的步骤相同，在“对象资源管理器”中选择要修改的“触发器”，右击鼠标，在弹出的快捷菜单中选择“修改”菜单项，打开“触发器脚本编辑”窗口，在该窗口中可以进行触发器的修改，修改后单击“执行”按钮重新执行即可。但是被设置成“WITH ENCRYPTION”的触发器是不能被修改的。

3．删除触发器

（1）删除DML触发器。以xs表的DML触发器为例，在“对象资源管理器”中展开“数据库”→“xsbook”→“表”→“dbo.xs”→“触发器”→选择要删除的触发器名称，右击鼠标，在弹出的快捷菜单中选择“删除”菜单项，在弹出的“删除对象”窗口中单击“确定”按钮，即可完成触发器的删除操作。

（2）删除DDL触发器。删除DDL触发器与删除DML触发器的方法类似，首先找到要删除的触发器，右击鼠标，选择“删除”选项即可。

先自我介绍一下，小编13年上师交大毕业，曾经在小公司待过，去过华为OPPO等大厂，18年进入阿里，直到现在。深知大多数初中级java工程师，想要升技能，往往是需要自己摸索成长或是报班学习，但对于培训机构动则近万元的学费，着实压力不小。自己不成体系的自学效率很低又漫长，而且容易碰到天花板技术停止不前。因此我收集了一份《java开发全套学习资料》送给大家，初衷也很简单，就是希望帮助到想自学又不知道该从何学起的朋友，同时减轻大家的负担。添加下方名片，即可获取全套学习资料哦