项目八 公司管理数据库系统中 触发器的使用

1. 项目八公司管理数据库系统中触发器的使用 终极目标：会利用触发器保证公司管理数据库系统数据的完整性

2. 一、促成目标 1．能正确理解触发器的概念、功能和类型 2．会使用企业管理器和Transact-SQL语句管理触发器 二、工作任务 利用触发器操作companyinfo数据库系统的数据，从而达到简化数据处理操作和提高数据安全性的目的。具体要求如下： 1．对p_order表创建名为reminder的触发器，当用户向p_order表中插入或修改记录时，向客户端发送一条提示消息：'修改数据？？？'。 2．对employee表创建名为emp_updtri的触发器，其功能是：若对雇员ID和姓名修改时，则给出“此数据未经许可不能修改!!! “的提示信息。

3. （一）触发器的概念 触发器是一种特殊类型的存储过程，一般存储过程通过用存储过程名称被直接调用而执行，而触发器主要是通过事件触发而被执行，触发器是一个功能强大的工具，它与表紧密相连，在表中数据发生变化时自动强制执行。触发器可以用于SQL Server约束、默认值和规则的完整性检查，还可以完成难以用普通约束实现的复杂功能。

4. 触发器的种类 （1)AFTER触发器：这类触发器在数据修改完成后被激活执行。其执行顺序为： 数据表约束检查 修改表总数据 激活触发器 (2) INSTEAD OF触发器：这类触发器会取代原来要进行的操作，在数据更改之前发生，数据如何更改完全取决于触发器的内容，其执行顺序为： 激活触发器 若触发器涉及数据更改，则检查表约束 注： INSTEAD OF触发器能够应用表及视图，而after触发器只能应用于表。

5. 使用触发器的限制 lCREATE TRIGGER语句必须是批处理的第一个语句；并且只能应用到一个表中。 l表的所有者具有创建触发器的缺省权限，表的所有者不能把该权限传给其它用户。 l触发器是数据库对象，所以其命名必须符合命名规则。 l触发器只能创建在当前数据库中，但触发器可以引用当前数据库的外部对象。 l一个触发器只能对应一个表，这是由触发器的机制决定的。 l触发器不能返回任何结果，为了阻止从触发器返回结果，不要在触发器定义中包含SELECT语句或变量赋值。

6. 创建触发器时需指定 • 名称。 • 在其上定义触发器的表。 • 触发器将何时激发。 • 激活触发器的数据修改语句。有效选项为 INSERT、UPDATE 或 DELETE。多个数据修改语句可激活同一个触发器。 • 执行触发操作的编程语句。

7. 使用Transact-SQL语句创建触发器 语法： CREATE TRIGGER trigger_name ON 表名或视图名 {FOR | AFTER | INSTEAD OF } {INSERT[，] | UPDATE[，] |DELETE } AS [IF UPDATE (列名1) [{AND | OR } UPDATE(列名2)[…n] sql语句

8. 参数： AFTER：表示只有在执行了指定的操作(INSERT, DELETE, UPDATE)之后触发器才被激活，执行触发器中的SQL语句。 INSTEAD OF：指定执行触发器而不是执行触发SQL 语句，从而替代触发语句的操作。 INSERT | UPDATE |DELETE：用来指明哪种数据操作将激活触发器。 IF UPDATE(列名)：用来测定对某一确定列是插入操作还是更新操作，但不与删除操作用在一起。

9. 在数据更新操作时，会产生两个幻表以记录更改前后的变化：INSERT表和DELETED表。 1、幻表: INSERTED表 和DELETED表

10. 2、使用 RAISERROR返回信息 返回用户定义的错误信息并设系统标志，记录发生错误。通过使用 RAISERROR 语句，客户端可以从 sysmessages 表中检索条目，或者使用用户指定的严重度和状态信息动态地生成一条消息。这条消息在定义后就作为服务器错误信息返回给客户端。 语法 RAISERROR (消息字符串 ,严重度，状态）

11. 3、事 务 控制 事务是将一系列操作变成独立的逻辑工作单元，其中任何一个语句执行时出错，系统都会自动回滚到事务开始前的状态，避免垃圾数据的产生。事务具有以下属性： (1)原子性：意味着对数据的修改，要么全都执行，要么全都不执行。 (2)一致性：完成事务后，所有的数据必须保持一致状态。 (3)隔离性：在并发环境中，各个事务是独立的。如果进行事务回滚操作，它能够重新装载起始数据，回到开始事务时刻的状态。 (4)持久性：提交事务后，它对于系统的影响是永久性的。即使出现系统故障，事务对数据的修改也将一直保持。

12. 事务控制语句有： （1）BEGIN TRAN语句 BEGIN TRAN语句表示事务开始，其语法格式为： BEGIN TRAN [transaction_name |@tran_name_variable[WITH MARK [‘description’]] 参数： • transaction_name为事务名。 • @tran_name_variable是用户定义的事务名称变量。 • WITH MARK 关键字指定在日志中标记事务，description是描述该标记的字符串。

13. （2）COMMIT语句 COMMIT语句表示提交事务，它使得自从事务开始以来所执行的所有数据修改成为数据库的永久部分，也标志一个事务的结束。 语法： COMMIT [TRAN SACTION] [transaction_name | @tran_name_variable]]

14. （3）ROLLBACK语句 ROLLBACK语句表示事务回滚到起点或指定的保存点处，清除自事务开始点或到某个保存点所做的所有数据修改，并且释放由事务控制的资源，也标志一个事务的结束。语法： ROLLBACK [TRANSACTION] [ transaction_name | ran_name_variable | savepoint_name | savepoint_variable]] 参数： • transaction_name和savepoint_name为保存点名。 • @savepoint_variable为含有保存点名的变量名，它们可用SAVE TRAN语句设置。

15. （4）SAVE TRAN语句 • SAVE TRAN语句表示设置保存点，其语法格式为： • SAVE TRAN[SACTION][ savepoint_name | savepoint_variable] • 参数： • savepoint_name为保存点名。 • @savepoint_variable为含有保存点名的变量名。 • 　　 用事务处理多条语句的编程思想是，事务开始后执行事务中一系列语句，如果全部成功，则最后提交事务。如果有一个语句执行时出错，则回滚到事务开始前的状态，出错前事务中执行的语句全部被撤销。如果事务中设置了保存点，在回滚时只是撤销出错点到保存点之间的语句，然后接着执行出错点下面的语句。

16. （二）创建触发器 1．使用企业管理器创建触发器 2. 利用Transact-SQL语句创建触发器

17. 任务1 对p_order表创建名为reminder的触发器，当用户向p_order表中插入或修改记录时，向客户端发送一条提示消息：'修改数据？？？'。 1）用鼠标右击p_order表|【所有任务】|【管理触发器(T)...】命令。 2）在打开的 “触发器属性”对话框中的“名称”文本框中选择“新建”，然后在文本框中输人以下创建触发器命令。 CREATE TRIGGER reminder ON p_order FOR INSERT, UPDATE AS RAISERROR ('修改数据？？？' , 16, 10) 3）单击“检查语法”检查语句是否正确。 4）单击“应用”按钮，在“名称”下拉列表中会有新创建的reminder触发器名字。 5）单击“确定”按钮,关闭窗口，创建成功。

18. （三）触发器的应用 1．使用INSERT触发器 INSERT触发器通常被用来验证被触发器监控的字段中的数据满足要求的标准，以确保数据完整性。 当触发INSERT触发器时，新的数据行就会被插入到触发器表和inserted表中。inserted表包含了INSERT语句中已记录的插入动作。触发器通过检查inserted表来确定是否执行触发器动作或如何执行它。

19. 【例2】 创建一个INSERT触发器，当在companyinfo数据库的employee表中插入一条新员工记录时，如果特长是“舞蹈”、“唱歌”、'绘画'的员工，则撤消该插入操作，并返回出错消息

20. 例2 程序清单 CREATE TRIGGER tri_InsertEmp ON Employee FOR INSERT AS DECLARE @hoobby varchar(20) SELECT @hoobby =inserted.特长 FROM inserted IF @hoobby ='舞蹈' OR @hoobby='唱歌' OR @hoobby ='绘画' BEGIN --返回用户定义的错误信息并设系统标志，记录发生错误 RAISERROR('不能插入非本公司设定部门的员工信息！',16,10) ROLLBACK TRANSACTION END

21. 观察操作结果： INSERT employee(姓名,性别,雇佣日期,特长,薪水) VALUES( '李亮', '男', '2001-7-20', '绘画', 4000) 程序2： INSERT employee(姓名,性别,雇佣日期,特长,薪水) VALUES( '洪皓', '男', '2001-7-21', '杂技', 4100)

22. 2．使用DELETE触发器 DELETE触发器通常用于两种情况，第一种情况是为了防止那些确实需要删除但会引起数据一致性问题的记录的删除。第二种情况是执行可删除主记录的子记录的级联删除操作。可以使用这样的触发器从主销售记录中删除所有的定单项。 当触发DELETE触发器后，从受影响的表中删除的行将被放置到deleted表中。

23. 【例3】创建一个名为d_emptri的触发器，其功能是：当对employee表进行删除操作时，首先检查要删除几行，若删除多行则返无法修改的信息。【例3】创建一个名为d_emptri的触发器，其功能是：当对employee表进行删除操作时，首先检查要删除几行，若删除多行则返无法修改的信息。 CREATE TRIGGER d_emptri ON employee FOR DELETE AS IF @@rowcount=0 return IF @@rowcount>1 BEGIN ROLLBACK TRANSACTION RAISERROR('you can only delete one information at one time',16,1) END RETURN

24. 【例4】创建一个名为employee_deleted的触发器，其功能是：当对employee表进行删除操作时，首先检查要订单表，如果删除的雇员没有接受订单，可以删除该雇员的消息，否则撤销删除，显示无法修改的信息。【例4】创建一个名为employee_deleted的触发器，其功能是：当对employee表进行删除操作时，首先检查要订单表，如果删除的雇员没有接受订单，可以删除该雇员的消息，否则撤销删除，显示无法修改的信息。 create trigger employee_deleted on employee for delete as if exists(select * from p_order join deleted on p_order.雇员id=deleted.雇员id) begin print '此雇员接收订单无法删除纪录' rollback transaction end

25. 3．使用UPDATE触发器 修改触发器和插入触发器的工作过程基本上一致，修改一条记录相当于插入了一条新的记录，删除一条旧的记录。 可将UPDATE语句看成两步操作：即捕获数据前像的DELETE语句，和捕获数据后像的INSERT语句。当在定义有触发器的表上执行UPDATE语句时，原始行（前像）被移入到deleted表，更新行（后像）被移入到inserted表。

26. 【例5】 创建了一个修改触发器，该触发器防止用户修改表employee的雇员ID。 CREATE TRIGGER employee_Update ON employee FOR UPDATE AS IF UPDATE (雇员ID) BEGIN RAISERROR ('You can not update this column.',10,1) ROLLBACK TRANSACTION END GO

27. 【例6】 在product表上创建了一个修改触发器，该触发器防止用户修改产品的库存量过大，如果库存量的变化超过100时给出错误提示，并回滚。 CREATE TRIGGER PRODUCT_INSERT_库存量 ON PRODUCT FOR UPDATE AS IF (SELECT MAX(ABS(INSERTED.库存量-DELETED.库存量)) FROM INSERTED JOIN DELETED ON INSERTED.产品ID=DELETED.产品ID )>100 BEGIN PRINT '库存量修改过大！' ROLLBACK TRANSACTION END

28. （四）修改触发器 1．使用企业管理器修改触发器正文，其操作步骤如下： （1）打开企业管理器中，展开指定的服务器和数据库； （2）选择指定的数据库和表，用右键单击要修改的表，从弹出的快捷菜单中选择“所有任务”子菜单下的“管理触发器”选项，则会出现触发器属性对话框。 （3）在名称选项框中选择要修改的触发器的名称，然后在文本框中修改触发器的SQL语句，单击“检查语法”按钮，可以检查语法是否正确。

29. 2．使用Transact-SQL语句修改触发器正文 语法： ALTER TRIGGER trigger_name ON table_name [ WITH ENCRYPTION ] FOR [AFTER | INSTEAD OF ] [ DELETE | INSERT | UPDATE ] AS sql_statements

30. （五）删除触发器 使用企业管理器 表---管理触发器选项--触发器属性对话框--选择要删除的触发器--单击“删除”按钮，即可删除该触发器。 使用系统命令DROP TRIGGER删除指定的触发器。 语法： DROP TRIGGER { trigger } [ ,...n ] 删除触发器所在的表时，SQL Server将会自动删除与该表相关的触发器。

31. （六）显示有关触发器的信息： 1．查看触发器的信息 sp_ help 'trigger_name' 显示触发器的所有者和创建时间 sp_ helptext 'trigger_name' 显示触发器的源代码 sp_depends ' trigger_name' 显示该触发器参考的对象清单

32. 2．修改触发器的名字 使用sp_rename命令，其语法格式为： EXEC sp_rename 'oldname', 'newname‘