【SQL server速成之路】触发器

🎉个人主页：这个昵称我想了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速成之路】索引与数据完整性

触发器

在这里插入图片描述
触发器不需要调用，当对一个表的特别事件出现时，它就会被激活。触发器是一类特殊的存储过程，用于保护表中的数据，当有操作影响到触发器保护的数据时，触发器将自动执行。

在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触发器

CREATETRIGGER[<架构名>.]<触发器名>ON<表名或视图名>/*指定操作对象*/[WITH  ENCRYPTION ]/*说明是否采用加密方式*/
    { FOR|AFTER| INSTEAD OF } 
    { [INSERT][,][UPDATE][,][DELETE] }            /*指定激活触发器的动作*/[NOTFORREPLICATION]/*说明该触发器不用于复制*/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”的信息。

/*使用带有提示消息的触发器*/IFEXISTS(SELECT name FROM sysobjects WHERE name ='reminder'ANDtype='TR')DROPTRIGGER reminder
GO
CREATETRIGGER reminder ON xs
    FORINSERT,UPDATEASBEGINDECLARE@strchar(50)SET@str='TRIGGER IS WORKING'PRINT@strEND
GO

向xs表中插入一行数据：

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

执行结果如图所示：
在这里插入图片描述

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

CREATETRIGGER update_trigger1
ON jy
FORUPDATEAS/*检查借书证号列或ISBN列是否被修改，如果有某些列被修改了，则取消修改操作*/IFUPDATE(借书证号)ORUPDATE(ISBN)BEGINPRINT'违背数据的一致性'ROLLBACKTRANSACTIONEND

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

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

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

CREATETABLE table1 (a int)
GO
CREATETRIGGER table1_insert
        ON table1 INSTEAD OFINSERTASPRINT'INSTEAD OF TRIGGER IS WORKING'

向表中插入一行数据：

INSERTINTO table1 VALUES(10)

执行结果如图所示。
在这里插入图片描述

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

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

查看INSERT 语句的执行结果：

SELECT*FROM View2
CREATETRIGGER trig ON View2 INSTEAD OFINSERTASBEGININSERTINTO books
    SELECT BookName, Color, Pages from inserted
END

结果如图所示。
在这里插入图片描述

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

CREATETRIGGER<触发器名称>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触发器，当删除一个表时，提示禁止该操作，然后回滚删除表的操作。

CREATETRIGGER safety
    ONDATABASEAFTER DROP_TABLE
    ASPRINT'不能删除该表'ROLLBACKTRANSACTION

尝试删除表table1：

DROPTABLE table1

执行结果如图所示：

在这里插入图片描述

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

CREATETRIGGER safety_server
    ONALL SERVER
    AFTER DROP_DATABASE
    ASPRINT'不能删除该数据库'ROLLBACKTRANSACTION

触发器的修改

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

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

ALTERTRIGGER<触发器名>ON<表名或视图名>[WITH ENCRYPTION ](FOR|AFTER| INSTEAD OF) 
        { [DELETE][,][INSERT][,][UPDATE] } 
    [NOTFORREPLICATION]AS  sql_statement [;][...n ]

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

ALTERTRIGGER<触发器名>ON { DATABASE|ALL SERVER } 
    [WITH ENCRYPTION ]
    { FOR|AFTER } { event_type [,...n ]| event_group } 
    AS   sql_statement [;]

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

ALTERTRIGGER reminder ON xs
    FORUPDATEASPRINT'执行的操作是修改'

触发器的删除

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

语法格式：

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

【例8】删除触发器reminder。

IFEXISTS(SELECT name FROM sysobjects WHERE name ='reminder'ANDtype='TR')DROPTRIGGER reminder

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

DROPTRIGGER safety ONDATABASE

界面方式操作触发器

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触发器的方法类似，首先找到要删除的触发器，右击鼠标，选择“删除”选项即可。

在这里插入图片描述

标签： sql 数据库 database

本文转载自: https://blog.csdn.net/m0_61684063/article/details/124798868
版权归原作者 这个昵称我想了20分钟 所有，如有侵权，请联系我们删除。

【SQL server速成之路】触发器

触发器

利用SQL命令创建触发器

触发器的修改

触发器的删除

界面方式操作触发器

发表评论

“【SQL server速成之路】触发器”的评论:

关于作者

overfit同步小助手

相关阅读

文章导航