触发器.PPT.ppt

《触发器.PPT.ppt》由会员分享，可在线阅读，更多相关《触发器.PPT.ppt（33页珍藏版）》请在三一文库上搜索。

1、触发器,内容,什么是触发器？ 触发器的作用 触发器的分类 触发器的创建 触发器的应用 触发器的注意问题,什么是触发器？,引言：当表或视图中的某些重要数据发生变化 时，为了保持数据的一致性和完整性，可以自动执行某段程序保证相关联其他数据也跟着进行相应的变化。能完成这种功能的程序就是触发器。 触发器是特殊类型的存储过程，它能在任何试图改变表或视图中由触发器保护的数据时执行。触发器主要通过操作事件(INSERT、UPDATE、DELETE)进行触发而被自动执行，不能直接调用执行，也不能被传送和接受参数。 触发器与表或视图是不能分开的，触发器定义在一个表或视图中，当在表或视图中执行插入（INSERT）

2、、修改（UPDATE）、删除（DELETE）操作时触发器被触发自动执行。当表或视图被删除时与它关联的触发器也一同被删除。,什么是触发器？,Ret_Emp 表,Derek,EMP 表,退休,什么是触发器？,EMP 表,Ret_Emp 表,插入,删除,触发器触发,触发器的作用,比较不同版本的数据 -读取其他数据库的表中的数据 在数据库中所有的相关表中级联所作的修改或删除 回滚无效的修改 强制实现比由CHECK约束提供的限制更为复杂的限制， 但是可以跨表、跨库、跨服务器 执行本地和远程存储过程 7. 强制引用完整性：尽可能使用声明性引用完整性（主外键），但是对于有些情况下（例如跨数据库、跨服务器、复

3、杂关系）DRI无法实现，还是需要使用触发器来实现强制的引用完整性。这种情况比较少,触发器的创建,创建触发器： CREATE TRIGGER Trigger_name ON table WITH ENCRYPTION FOR/AFTER DELETE, INSERT, UPDATE AS Sql_statements 注意：FOR和AFTER作用相同，我们建议使用AFTER，因为别的数据库也支持。,触发器的分类,DML触发器： Insert触发器：插入操作时触发 Update触发器：修改操作时触发 Delete触发器：删除操作时触发 Instead of触发器：替代视图操作时触发 级联触发器：实

4、现级联操作。 嵌套触发器：一个触发器触发下一个触发器，依此类推。 DDL触发器： Create、Alter、Drop：DDL操作时触发,触发器的应用,使用 Inserted 和 Deleted 表 触发器可以访问两个逻辑表 Inserted表 Deleted表 Inserted 和Deleted 表包含更新前和更新后的数据映像 Inserted和Deleted表中不包含表中不受更新操作 影响的数据 Inserted表和Deleted表在内存的缓冲区里,触发器的应用,Insert触发器： 当试图向表中插入数据时，将执行 INSERT 触发器 INSERT 触发器执行下列操作： 向Inserted

5、表中插入一个新行的副本。 检查Inserted 表中的新行，确定是否要阻止该插入操作。 如果所插入的行中的值是有效的，则将该行插入到触发器表中。,触发器的应用,Insert触发器： 例： CREATE TRIGGER CheckRoyalty ON roysched FOR INSERT AS IF (SELECT royalty FROM inserted) 30 BEGIN PRINT RoyaltyTrigger：版权费不能超过 30 PRINT 请将版权费修改为小于 30 的值 ROLLBACK TRANSACTION END,触发器的应用,UPDATE 触发器： 当试图更新表中的数据

6、时，将执行UPDATE触发器 UPDATE 触发器执行下列操作： 将原始数据行移到逻辑Deleted表中 将一个新行插入Inserted表中，然后插入触发器表中 计算Deleted表和Inserted表中的值以确定是否需要进行干预 可以创建Update触发器以验证对单个列或整个表的更新,触发器的应用,UPDATE 触发器： 例：列级 UPDATE 触发器 CREATE TRIGGER NoUpdatePayterms ON sales FOR UPDATE AS IF UPDATE (payterms) -如果修改了某列 BEGIN PRINT 不能修改订单的付费条款 ROLLBACK TRA

7、NSACTION END,触发器的应用,UPDATE 触发器： 例：表级 UPDATE 触发器： CREATE TRIGGER NoUpdateDiscount ON discounts FOR UPDATE AS IF (SELECT discount FROM inserted) 12 BEGIN PRINT 不能指定大于 12% 的折扣 ROLLBACK TRANSACTION END,触发器的应用,DELETE 触发器： 当试图从表中删除数据时，将执行DELETE 触发器。 DELETE触发器执行下列操作： 从触发器表中删除行。 将删除的行插入到Deleted表中。 检查Deleted

8、表中的行，以确定是否需要或应如何执行触发器操作。,触发器的应用,DELETE 触发器： 例： CREATE TRIGGER NoDelete9901 ON pub_info FOR DELETE AS IF (SELECT pub_id FROM deleted) = 9901 BEGIN PRINT 不能删除出版商 9901 的详细信息 ROLLBACK TRANSACTION END,触发器的应用,级联触发器：了解 单独的 DELETE 或 UPDATE 语句可启动一系列级联引用操作。例如，假设有一个数据库包含三个表：TableA、TableB 和 TableC。针对 TableA 中的主

9、键，用 ON DELETE CASCADE 定义 TableB 中的外键。针对 TableB 中的主键，用 ON DELETE CASCADE 定义 TableC 中的外键。如果 DELETE 语句删除 TableA 中的行，则该操作也将删除 TableB 中具有与 TableA 中所删除的主键匹配的任何外键中的所有行，然后删除 TableC 中具有与 TableB 中所删除的主键匹配的任何外键中的所有行。 由单个 DELETE 或 UPDATE 触发的一系列级联引用操作必须构成不包含循环引用的树。在 DELETE 或 UPDATE 所产生的所有级联引用操作的列表中，每个表只能出现一次。级联引

10、用操作树到任何给定表的路径必须只有一个。树的任何分支在遇到指定了 NO ACTION 或默认为 NO ACTION 的表时终止。,触发器的应用,嵌套触发器： 如果一个触发器在执行操作时会启动另一个触发器，而第二个触发器又接着启动下一个触发器，以次类推，些触发器称为嵌套触发器。 触发器最多可以嵌套 32 层。 要启用触发器嵌套，请以以下方式执行 sp_configure 系统存储过程： sp_configure nested trigger, 1 要禁用触发器嵌套，请执行下面的语句： sp_configure nested trigger, 0,触发器的应用,INSTEAD OF 触发器： 包含

11、代替原始数据操作语句的代码 INSTEAD OF触发器的主要优点是可以使不能更新的视图支持更新 INSTEAD OF触发器的另一个优点是使得以编写这样的逻辑代码：可以拒绝批处理中的某些部分同时允许批处理的其它部分成功,触发器的应用,INSTEAD OF 触发器： 视图定义示例： CREATE VIEW Emp_pub AS SELECT emp_id, lname, job_id, pub_name FROM employee e, publishers p WHERE e.pub_id = p.pub_id 触发器定义示例： CREATE TRIGGER del_emp ON Emp_pub

12、 INSTEAD OF DELETE AS DELETE employee WHERE emp_id IN (SELECT emp_id FROM DELETED),其它示例,触发器的应用,DDL触发器： 一： Microsoft SQL Server 2005 在数据库引擎 中引入了多项能提高程序员的开发能力和工作效率的改进和新功能。其中DDL触发器就是其中之一。 除了对修改数据的数据操作语言语句（INSERT、UPDATE 和 DELETE）所激发的触发器提供支持外，数据库引擎 还包括一类新的触发器，由修改数据库对象的 DDL 语句（如以 CREATE、ALTER 或 DROP 开头的语句

13、）激发。DDL 触发器可用于审核或控制对数据库架构的更改。 像常规触发器一样，DDL 触发器将激发存储过程以响应事件，这些语句主要是以 CREATE、ALTER 和 DROP 开头的语句。DDL 触发器可用于管理任务，例如审核和控制数据库操作。 仅在运行触发 DDL 触发器的 DDL 语句后，DDL 触发器才会激发。DDL 触发器无法作为 INSTEAD OF 触发器使用。 二： 在响应当前数据库或服务器中处理的 Transact-SQL 事件时，可以激发 DDL 触发器。触发器的作用域取决于事件。例如，每当数据库中发生 CREATE TABLE 事件时，都会触发为响应 CREATE TABL

14、E 事件创建的 DDL 触发器。每当服务器中发生 CREATE LOGIN 事件时，都会触发为响应 CREATE LOGIN 事件创建的 DDL 触发器。,触发器的应用,DDL触发器： 在下面的示例中，每当数据库中发生 DROP TABLE 事件或 ALTER TABLE 事件，都将触发 DDL 触发器 safety： CREATE TRIGGER safety ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT You must disable Trigger safety to drop or alter tables! ROLLBACK ;

15、数据库范围内的 DDL 触发器都作为对象存储在创建它们的数据库中。可以在 master 数据库中创建 DDL 触发器，这些 DDL 触发器的行为与在用户设计的数据库中创建的 DDL 触发器一样。可以从创建 DDL 触发器的数据库上下文中的 sys.triggers 目录视图中，或通过指定数据库名称作为标识符（例如 master.sys.triggers）来获取有关这些 DDL 触发器的信息。 服务器范围内的 DDL 触发器作为对象存储在 master 数据库中。不同的是，可以从任何数据库上下文中的 sys.server_triggers 目录视图中获取有关数据库范围内的 DDL 触发器的信息。

16、 使用 EVENTDATA 函数，可以捕获有关激发 DDL 触发器的事件的信息。此函数返回 xml 值,触发器的应用,DDL触发器： 三： 以下示例创建用于存储所有数据库级事件的相关信息的表，并在表中填充 DDL 触发器。通过对 EVENTDATA 生成的 XML 数据使用 XQuery，可以捕获事件类型和 Transact-SQL 语句。 USE AdventureWorks;GOCREATE TABLE ddl_log (PostTime datetime, DB_User nvarchar(100), Event nvarchar(100), TSQL nvarchar(2000);GO

17、CREATE TRIGGER log ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS ASDECLARE data XMLSET data = EVENTDATA()INSERT ddl_log (PostTime, DB_User, Event, TSQL) VALUES (GETDATE(), CONVERT(nvarchar(100), CURRENT_USER), data.value(/EVENT_INSTANCE/EventType)1, nvarchar(100), data.value(/EVENT_INSTANCE/TSQLCommand)

18、1, nvarchar(2000) ) ;GO-Test the trigger.CREATE TABLE TestTable (a int)DROP TABLE TestTable ;GOSELECT * FROM ddl_log ;GO-Drop the trigger.DROP TRIGGER logON DATABASEGO-Drop table ddl_log.DROP TABLE ddl_logGO,SQL Server 2005中的DDL触发器的实现,触发器的应用,触发器执行顺序,触发器的应用,禁用触发器： 1、如果是大批量的导入数据，而且确认数据的正确性没有问题。那么我们在数据

19、导入的过程中可以禁用触发器提高数据导入的速度。 2、一定要确认在数据导入的过程中，没有用户在操作数据，因此最好是进入单用户模式或者DBO_ONLY模式。 alter table dbo.member disable trigger testTrigger,触发器的应用,触发器激发顺序：了解 1、在7.0以前，在一个表上的一个操作上（INSERT,DELETE,UPDATE）只能有一个触发器。例如在test表上的insert操作只能有一个触发器。 2、只能为AFTER提供触发器 3、只能为表提供触发器 4、可以指定触发器为FIRST、LAST、剩余的就是未定义 5、保证FIRST首先执行、LAS

20、T最后执行，NONE在两者之间执行，但是不能保证NONE之间的执行顺序,触发器的注意问题,性能考虑： 1、触发器一般比起CHECK约束的性能会差一些 2、触发器和激发他的进程之间属于同一个事务，激发进程所锁定的所有资源对于触发器来说都是可以访问的，因为都属于同一个事务 3、ROLLBACK回滚的是整个事务，而不是触发器 4、即使是在触发器中显式的开始一个事务，回滚时也是回滚整个事务。 5、保持短小精悍，因为触发器和激发进程在同一个事务中，触发器没有完成，整个事务不会提交，因此触发器要短小精悍。,触发器的注意问题,性能考虑： create trigger trCu on dbo.Customer

21、s for insert as begin tran print test rolback rollback tran insert into dbo.Customers values(1000,xkj) select * from dbo.Customerswhere Name = xkj,触发器的注意问题,不能在触发器中使用的 SQL 语句,触发器的注意问题,创建触发器的指导原则 触发器可以与对表执行的三个操作（INSERT、 UPDATE 和 DELETE）相关联 一个触发器只应用于单独一个表 WITH ENCRYPTION选项可用于对用户隐藏触发器 的定义。但是，加密的触发器无法进行解密 触发器可以引用视图或临时表，但不能和它们相关联 触发器可以包含任意数量的 SQL 语句 默认情况下，只有数据库所有者才具有创建触发器的权限。此权限不可转让。 触发器只能在当前数据库中创建。 但是，触发器可以引用其他数据库中的对象。,总结,什么是触发器？ 触发器的分类 触发器的创建 触发器的应用 触发器的注意问题,作业,什么是触发器？ 触发器的分类有哪些？ Inserted表和Deleted表的作用？ 区分各种触发器的使用？ 使用触发器应注意哪些问题？,谢谢！,