🎉个人主页:这个昵称我想了20分钟
✨往期专栏:【速成之路】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速成之路】索引与数据完整性
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/05120adf9dad1d83b8926da7272c2029.png)
触发器不需要调用,当对一个表的特别事件出现时,它就会被激活。触发器是一类特殊的存储过程,用于保护表中的数据,当有操作影响到触发器保护的数据时,触发器将自动执行。
在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)触发器激活的时机
① 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
向xs表中插入一行数据:
INSERT INTO xs VALUES('141101','吴越',1,'1996-06-20', ,'英语',0,NULL)
执行结果如图所示:
【例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
注: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'
向表中插入一行数据:
INSERT INTO table1 VALUES(10)
执行结果如图所示。
【例4】 在xsbook数据库中创建表、视图和触发器,以说明INSTEAD OF INSERT触发器的使用。
如果对引用View2视图的INSERT语句的每一列都指定值,例如:
INSERT INTO View2 (BookKey ,BookName ,Color, ComputedCol ,Pages)
VALUES (4, '计算机辅助设计', '红色', '绿色',100)
查看INSERT 语句的执行结果:
SELECT * FROM View2
CREATE TRIGGER trig ON View2 INSTEAD OF INSERT
AS
BEGIN
INSERT INTO books
SELECT BookName, Color, Pages from inserted
END
结果如图所示。
2.创建DDL触发器
语法格式:
CREATE TRIGGER <触发器名称>
ON { ALL SERVER | DATABASE }
[ WITH ENCRYPTION ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS sql_statement [ ; ] [ ...n ]
说明:
- 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
尝试删除表table1:
DROP TABLE table1
执行结果如图所示:
【例6】 创建服务器作用域的DDL触发器,当删除一个数据库时,提示禁止该操作并回滚删除数据库的操作。
CREATE TRIGGER safety_server
ON ALL SERVER
AFTER DROP_DATABASE
AS
PRINT '不能删除该数据库'
ROLLBACK TRANSACTION
触发器的修改
要修改触发器执行的操作,可以使用ALTER TRIGGER语句。
(1)修改DML触发器的语法格式:
ALTER TRIGGER <触发器名>
ON <表名或视图名>
[ WITH ENCRYPTION ]
( FOR | AFTER | INSTEAD OF )
{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }
[ NOT FOR REPLICATION ]
AS sql_statement [ ; ] [ ...n ]
(2)修改DDL触发器的语法格式:
ALTER TRIGGER <触发器名>
ON { DATABASE | ALL SERVER }
[ WITH ENCRYPTION ]
{ FOR | AFTER } { event_type [ ,...n ] | event_group }
AS sql_statement [ ; ]
【例7】 修改xsbook数据库中在xs表上定义的触发器reminder。
ALTER TRIGGER reminder ON xs
FOR UPDATE
AS PRINT '执行的操作是修改'
触发器的删除
触发器本身是存在表中的,因此,当表被删除时,表中的触发器也将一起被删除。删除触发器使用DROP TRIGGER语句。
语法格式:
DROP TRIGGER <触发器名> [ ,...n ] [ ; ] /*删除DML触发器*/
DROP TRIGGER <触发器名> [ ,...n ] ON { DATABASE | ALL SERVER }[ ; ] /*删除DDL触发器*/
【例8】 删除触发器reminder。
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'reminder' AND type = 'TR')
DROP TRIGGER reminder
【例9】 删除DDL触发器safety。
DROP TRIGGER safety ON DATABASE
界面方式操作触发器
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触发器的方法类似,首先找到要删除的触发器,右击鼠标,选择“删除”选项即可。