第11章_触发器

1、第11章 触发器 学习要点：学习要点：创建触发器 修改触发器 删除触发器等内容 11.1 概述概述 11.2 创建触发器创建触发器 11.3 管理触发器管理触发器第11章 触发器 11.1.1 触发器的概念触发器的概念 触发器（trigger）是一种特殊的存储过程，它不同于一般的存储过程。一般的存储过程通过存储过程名称被直接调用，而触发器主要是通过事件进行触发而被执行。触发器是一个功能强大的工具，与表紧密连接，可以看作是表格定义的一部分。当用户修改（INSERT、UPDATE或DELETE）指定表或视图中的数据时，该表中的相应的触发器就会自动执行。触发器基于一个表创建，但可以操作多个表。触发器

2、常用来实现复杂的商业规则。第11章 触发器 11.1.2 触发器的优点触发器的优点1触发器是自动执行的，当用户对表中的数据作了任何修改（比如手工输入或者应用程序采取的操作）之后立即被激活。 2触发器可以通过数据库中的相关表进行层叠更改，实现多个表之间数据的一致性和完整性。 3触发器可以强制限制，这些限制比用CHECK约束所定义的更复杂。 第11章 触发器 11.1.3 触发器的分类触发器的分类 v AFTER触发器触发器：又称后触发器（又称后触发器（After Trigger）这种类型的触发器将在数据变动（UPDATE、INSERT和DELETE操作）完成后才被触发。指定 AFTER 与指定

3、FOR 相同。 AFTER触发器只能在表上定义。在同一个数据表中可以创建多个AFTER触发器。 Server2000默认的为AFTER触发器。 第11章 触发器 v INSTEAD OF触发器：触发器： 又称前触发器（又称前触发器（Inserted Of Trigger） INSTEAD OF触发器在数据变动以前被触发，并取代变动数据的操作（UPDATE、INSERT和DELETE操作），而去执行触发器定义的操作。 INSTEAD OF触发器可以在表或视图上定义。 在表或视图上，每个UPDATE、INSERT和DELETE语句最多可以定义一个INSTEAD OF触发器。第11章 触发器 del

4、eted 表和表和 inserted 表表v deleted 表用于存储 DELETE 和 UPDATE 语句所影响的行的备份。在执行 DELETE 或 UPDATE 语句时，行从基本表中删除，并转移到 deleted 表中。deleted 表和基本表通常没有相同的行。 v inserted 表用于存储 INSERT 和 UPDATE 语句所影响的行的备份。在一个插入或更新事务处理中，新建行被同时添加到 inserted 表和基本表中。inserted表中的行是基本表中新行的备份。 第11章 触发器 创建触发器前应考虑下列问题：创建触发器前应考虑下列问题： v CREATE TRIGGER 语

5、句必须是批处理中的第一个语句。v 创建触发器的权限默认分配给表的所有者，且不能将该权限转给其他用户。 v 触发器为数据库对象，其名称必须遵循标识符的命名规则。 v 虽然触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建触发器。 第11章 触发器 v 虽然不能在临时表或系统表上创建触发器，但是触发器可以引用临时表。 v 在含有用 DELETE 或 UPDATE 操作定义的外键的表中，不能定义 INSTEAD OF DELETE和 INSTEAD OF UPDATE 触发器。 v 虽然 TRUNCATE TABLE 语句类似于没有 WHERE 子句（用于删除行）的 DELETE 语句，但

6、它并不会引发 DELETE 触发器，因为 TRUNCATE TABLE 语句没有记录。 v WRITETEXT 语句不会引发 INSERT 或 UPDATE 触发器。 第11章 触发器 创建触发器时需指定：创建触发器时需指定： v 触发器名称。v 在其上定义触发器的表。 v 触发器将何时激发。 v 激活触发器的数据修改语句。有效选项为 INSERT、UPDATE 或 DELETE。多个数据修改语句可激活同一个触发器。v 执行触发操作的编程语句。 第11章 触发器 11.2.1 使用使用SQL-Server企业管理器创建触发器企业管理器创建触发器 （1）展开服务器组，然后展开服务器。这里选中sa

7、les数据库。（2）展开“数据库”文件夹，展开含触发器的表所属的数据库，然后单击“表”节点。（3）右击将在其上创建触发器的表，这里选中Salers表，弹出快捷菜单，选择“所有任务”菜单，然后单击“管理触发器”命令。出现“触发器属性”对话框，如图11-1所示。 （4）在“名称”中，单击 “新建”。（5）在“文本”框中输入触发器的文本。用 CTRL+TAB 键来缩进触发器的文本。 第11章 触发器 图11-1 “触发器属性”对话框 第11章 触发器 （6）单击“应用”按钮，在名称下拉列表中出现新创建的TR_UpdateSalers触发器的名称，单击“确定”按钮，即可关闭该对话框，成功创建触发器。如

8、图11-2所示。 图11-2 “触发器属性”对话框 第11章 触发器 11.2.2 使用使用Transact-SQL语句创建触发器语句创建触发器 CREATE TRIGGER trigger_name ON table | view WITH ENCRYPTION FOR | AFTER | INSTEAD OF INSERT , UPDATE WITH APPEND NOT FOR REPLICATION AS IF UPDATE ( column ) AND | OR UPDATE ( column ) .n sql_statement .n 第11章 触发器 v trigger_name

9、：是触发器的名称 。v Table | view：是在其上执行触发器的表或视图 。v AFTER：指定触发器只有在触发 SQL 语句中指定的所有操作都已成功执行后才激发。如果仅指定 FOR 关键字，则 AFTER 是默认设置。 v INSTEAD OF：指定执行触发器而不是执行触发 SQL 语句，从而替代触发语句的操作。 v AS：是触发器要执行的操作。 v sql_statement：是触发器的条件和操作。 v IF UPDATE (column)：测试在指定的列上进行的 INSERT 或 UPDATE 操作，不能用于 DELETE 操作 。第11章 触发器 例11-2 创建一个AFTER触

10、发器，要求实现以下功能：在OrderDetails表上创建一个插入类型的触发器TR_insertOrderDetails，当在OrderDetails表中插入记录时，自动计算产品的总价格，并将销售员信息和总价格更新到销售员业绩表TotalSales表中。 CREATE TRIGGER TR_insertOrderDetails ON dbo.OrderDetails FOR INSERTASBEGINdeclare orderid int,pid char(6),quantity intdeclare price moneydeclare sid char(3) select orderid=

11、Orderid,pid=productid,quantity=quantity from insertedselect price=price from products where productid=pidselect sid=saleid from orders where orderid=orderidif exists(select * from totalsales where saleid=sid)update totalsales set total=total+quantity*price where saleid=sidelse insert into totalsales

12、 values(sid,quantity*price)END第11章 触发器 例11-3 创建一个AFTER触发器，要求实现以下功能：在Order表上创建一个删除类型的触发器TR_deleteOrder，当在Order表中删除某个订单的记录时，自动删除OrderDetails表中与此订单编号对应的记录。 CREATE TRIGGER TR_deleteOrder ON dbo.Orders FOR DELETE ASdeclare OrderID intselect OrderID=OrderID from DeletedPrint 开始查找并删除表OrderDetails中的相关记录.Del

13、ete from OrderDetails Where OrderID=OrderIDPrint 删除表OrderDetails中的相关记录条数为+str(rowcount)+条第11章 触发器 11.3.1 查看触发器查看触发器 1使用企业管理器查看触发器信息使用企业管理器查看触发器信息 （1）查看触发器定义信息。）查看触发器定义信息。1）展开服务器组，然后展开服务器。2）展开“数据库”文件夹，展开含触发器的表所属的数据库，然后单击“表”文件夹。3）在详细信息窗格中，右击触发器所在的表，指向“所有任务”菜单，然后单击“管理触发器”命令第11章 触发器 （2）查看触发器的相关性）查看触发器的相

14、关性 1）展开服务器组，然后展开服务器。2）展开“数据库”文件夹，展开触发器所属的数据库，然后单击“表”文件夹。3）在详细信息窗格中，右击触发器所属的表，指向“所有任务”菜单，然后单击“显示相关性”命令。 4）在“对象”中，单击要查看其相关性的触发器。 第11章 触发器 2 2使用系统存储过程查看触发器使用系统存储过程查看触发器 sp_help sp_helptext sp_depends sp_helptrigger第11章 触发器 11.3.2 修改触发器修改触发器 1 1使用企业管理器修改触发器的正文使用企业管理器修改触发器的正文 在详细信息窗格中，右击将在其上创建触发器的表，指向“所有

15、任务”菜单，然后单击“管理触发器”命令。在“名称”中框中选择触发器的名称。 在“文本”框中更改触发器的文本。用 CTRL+TAB 键来缩进触发器的文本。 若要检查语法，单击“检查语法”命令。单击“确定”按钮，即可关闭该对话框，成功修改触发器。 第11章 触发器 2 2使用使用Transact-SQLTransact-SQL语句修改触发器的正文语句修改触发器的正文 ALTER TRIGGER trigger_name 3 3使用系统存储过程修改触发器的名称使用系统存储过程修改触发器的名称 sp_rename old_name，new_name例如：例如： USE sales GOsp_rename TR_insertOrderDetails, R_OrderDetails_InsertGO第11章 触发器 11.3.3 删除触发器删除触发器 1.使用企业管理器删除触发器使用企业管理器删除触发器2.2.使用使用Transact-SQLTransact-SQL语句删除触发器语句删除触发器DROP TRIGGER trigger ,.n v trigger：是要删除的触发器名称。USE salesIF EXI