【个人学习笔记18之--浅谈触发器】

这次谈的是触发器

Microsoft SQL Server 提供两种主要机制来强制使用业务规则和数据完整性：约束和触发器。

关于触发器和约束用谁比较好,这主要看业务的逻辑复杂程度.
如果你的处理逻辑比较简单,并且可以用一些简单的约束来处理,则应该尽量使用PRIMARY KEY ,UNIQUE CHECK这些约束.
当约束支持的功能无法满足应用程序的功能要求时，DML 触发器非常有用 例如:
1.除非 REFERENCES 子句定义了级联引用操作,否则 FOREIGN KEY 约束只能用与另一列中的值完全匹配的值来验证列值
2.约束只能通过标准化的系统错误消息来传递错误消息。如果应用程序需要（或能受益于）使用自定义消息和较为复杂的错误处理，则必须使用触发器。
3.DML 触发器可以防止恶意或错误的 INSERT、UPDATE 以及 DELETE 操作，并强制执行比 CHECK 约束定义的限制更为复杂的其他限制。
 check约束只能检查引用表内的列,当涉及外表的一些规则时候 需要在本表里使用trigger.
4.如果触发器表上存在约束，则在 INSTEAD OF 触发器执行后但在 AFTER 触发器执行前检查这些约束。
  如果违反了约束，则回滚 INSTEAD OF 触发器操作并且不执行 AFTER 触发器。
5.一个表中的多个同类 DML 触发器（INSERT、UPDATE 或 DELETE）允许采取多个不同的操作来响应同一个修改语句。

 

 

触发器分类：(- -|| 这个分类 可能是错的 )
1.DDL触发器:当服务器或数据库中发生数据定义语言 (DDL) 事件时将调用 DDL 触发器
2.登录触发器:将为响应 LOGON 事件而激发存储过程。与 SQL Server 实例建立用户会话时将引发此事件。
3.DML触发器:当数据库中发生数据操作语言 (DML) 事件时将调用 DML 触发器
 这个类型触发器还可以分成:
 AFTER 触发器:在执行了INSERT、UPDATE 或 DELETE 语句操作之后执行 AFTER 触发器（指定 AFTER 与指定 FOR 相同）
 INSTEAD OF 触发器 :执行 INSTEAD OF 触发器代替通常的触发动作。还可为带有一个或多个基表的视图定义 INSTEAD OF 触发器
 clr触发器:CLR 触发器可以是 AFTER 触发器或 INSTEAD OF 触发器。CLR 触发器还可以是 DDL 触发器。
      CLR 触发器将执行在托管代码（在 .NET Framework 中创建并在 SQL Server 中上载的程序集的成员）中编写的方法
      
先来说说我们最常用的DML触发器

这里只介绍DML中的 AFTER触发器和INSTEAD OF触发器.
首先列出2者的不同点:
函数                                                            AFTER 触发器                                                            INSTEAD OF 触发器 
~适用范围                                                          表                                                                            表和视图

~每个表或视图包含触发器的数量         每个触发操作（UPDATE、DELETE 和 INSERT）                   每个触发操作（UPDATE、DELETE 和 INSERT）
                                                                  包含多个触发器                                                             包含一个触发器
                                         
~级联引用                                                   无任何限制条件                                                    不允许在作为级联引用完整性约束目标的表上使用
                                                                                                                                            INSTEAD OF UPDATE 和 DELETE 触发器
~执行                                          晚于：  约束处理                                                               早于:   约束处理
                                                            声明性引用操作                                                       替代：  触发操作
                                                            创建插入的和删除的表(反映对基表所做的更改)              晚于：  创建插入的和删除的表(反映对基表所做的更改)
                                                            触发操作
                                          
~执行顺序                                             可指定第一个和最后一个执行                                           不适用

~插入的和删除的表中的 varchar(max)                 允许                                                           允许
 nvarchar(max) 和 varbinary(max) 列引用。
                                                                 
~插入的和删除的表中的 text、ntext                 不允许                                                          允许
 和 image 列引用。
 
~ 递归性                                                                可以                                             在内部再次触发该触发器后不会再递归触发,但是可以触发
                                                                                                                                        将启动一系列约束操作和 AFTER 触发器执行    

触发时间:(上面对比里说了,再强调下)
AFTER触发器:在处理触发操作（INSERT、UPDATE 或 DELETE）、INSTEAD OF 触发器和约束之后激发。
            ps：因为是在约束后激发,因此触发器里不可以进行违反约束的操作，这样会使约束失去效果.
INSTEAD OF触发器:将在处理约束前激发，以替代触发操作。如果表有 AFTER 触发器，它们将在处理约束之后激发。
                 如果违反了约束，将回滚 INSTEAD OF 触发器操作并且不执行 AFTER 触发器
                
创建一个触发器的几点：（MSDN）
1.CREATE TRIGGER 语句必须是批处理中的第一个语句，该语句后面的所有其他语句被解释为 CREATE TRIGGER 语句定义的一部分。
2.创建 DML 触发器的权限默认分配给表的所有者，且不能将该权限转给其他用户。
3.DML 触发器为数据库对象，其名称必须遵循标识符的命名规则。
4.虽然 DML 触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建 DML 触发器。
5.虽然 DML 触发器可以引用临时表，但不能对临时表或系统表创建 DML 触发器。不应引用系统表，而应使用信息架构视图。
6.对于含有用 DELETE 或 UPDATE 操作定义的外键的表，不能定义 INSTEAD OF DELETE 和 INSTEAD OF UPDATE 触发器。
7.虽然 TRUNCATE TABLE 语句类似于不带 WHERE 子句的 DELETE 语句（用于删除所有行），但它并不会触发 DELETE 触发器，因为 TRUNCATE TABLE 语句没有记录。
8.WRITETEXT 语句不会触发 INSERT 或 UPDATE 触发器。
9.尽量不要在触发器里面返回结果.这是因为对这些返回结果的特殊处理必须写入每个允许对触发器表进行修改的应用程序中(未来版本的 SQL Server 中会删除从触发器返回结果集的功能)

触发器的延迟的名称解析
 还记得上一个笔记的存储过程的延迟的名称解析,它在触发器中一样适用的. 
 你可以在触发器中适用还没有定义的表,触发器回等到第一次触发触发器的时候编译并且检查内部的表.这个时候如果还不存在,那就出错了.
  如果 DML 触发器引用的对象已删除或重命名，则执行触发器时也会返回错误。
  如果 DML 触发器中引用的对象被替换为同名对象，则不必重新创建即可执行触发器。
 
触发器触发的先后
 如果一个表上的DML操作的一个类型对应了多个触发器,则谁先触发呢？
 你可以通过sp_settriggerorder这个存储过程 指定对应操作触发器的第一个触发和最后一个触发(INSTEAD OF触发器除外)
 例:sp_settriggerorder @triggername = 'MyTrigger', @order = 'first', @stmttype = 'UPDATE' 具体用法参考MSDN
 ps:不能将 INSTEAD OF 触发器指定为第一个或最后一个触发器,因为它可能激发表的AFTER触发器.
 
触发器的整体性(指事务方面)
 create table a (id int,value1 int) create table b (id int,value2 int ) go --建立在b上的触发器 create trigger b_t on b after insert as rollback; print 'no' go --在一个事务里执行操作 begin tran insert a select 1,2 insert b select 3,4 commit tran --插入B表操作 触发AFTER触发器 --因为触发器的操作属于事务的一部分,触发器内又有回滚操作,所以回滚整个事务 select * from a /* id value1 ----------- ----------- (0 行受影响)*/ --如果不是在事务内进行 仅仅回滚触发器内的操作和触发操作 insert a select 1,2 insert b select 3,4 select * from a /* id value1 ----------- ----------- 1 2*/

 

 

触发器中的2张特殊表--Inserted 和 Deleted
 Inserted表包含的是update 和 insert 操作保留的数据(AFTER 是操作后的数据 INSTEAD of是想操作的数据)
 Deleted表包含的是 update 和 delete 操作保留的数据(AFTER 是操作后的数据 INSTEAD of是想操作的数据)
 所以利用这2个表是可以判断你触发操作的类型 比如你的触发器是这么定义的 for insert , update, delete
 你可以在触发器内部用 ：
  IF EXISTS(SELECT * FROM inserted) BEGIN IF EXISTS(SELECT * FROM deleted) BEGIN PRINT 'UPDATE identified'; END ELSE BEGIN PRINT 'INSERT identified'; END END ELSE BEGIN PRINT 'DELETE identified'; END GO 

  还有：
  这2个表因为没有索引,当你需要在2个表里大量检索数据时候,你需要索引 这里处理的手段是将表数据导入到临时表 然后给临时表加索引.
  这里就说一点:2000里 这2个表是事务日志的视图.访问的时候扫描的是事务日志。
                    2005里 这2个表指向tempdb中的行版本数据.
 
获取DML触发器的信息
  --获取有关数据库中的触发器的信息
  select * from sys.triggers
  --获取有关激发触发器的事件的信息
  select * from sys.trigger_events
  select * from sys.events --这包括同时激发事件通知和触发器的事件。
  --查看触发器的定义
  select * from sys.sql_modules
  sp_helptext 
  --查看触发器的依赖关系
  sys.sql_expression_dependencies
  sys.dm_sql_referenced_entities 
  sys.dm_sql_referencing_entities

 

 

禁用触发器
 --禁用当前数据库中所有数据库级别的 DDL 触发器：
  DISABLE TRIGGER ALL ON DATABASE
 --禁用服务器实例中所有服务器级别的 DDL 触发器：
  DISABLE TRIGGER ALL ON ALL SERVER
 --禁用当前数据库中的所有 DML 触发器：
  DECLARE @schema_name sysname, @trigger_name sysname, @object_name sysname ; DECLARE @sql nvarchar(max) ; DECLARE trig_cur CURSOR FORWARD_ONLY READ_ONLY FOR SELECT SCHEMA_NAME(schema_id) AS schema_name, name AS trigger_name, OBJECT_NAME(parent_object_id) as object_name FROM sys.objects WHERE type in ('TR', 'TA') ; OPEN trig_cur ; FETCH NEXT FROM trig_cur INTO @schema_name, @trigger_name, @object_name ; WHILE @@FETCH_STATUS = 0 BEGIN SELECT @sql = 'DISABLE TRIGGER ' + QUOTENAME(@schema_name) + '.' + QUOTENAME(@trigger_name) + ' ON ' + QUOTENAME(@schema_name) + '.' + QUOTENAME(@object_name) + ' ; ' ; EXEC (@sql) ; FETCH NEXT FROM trig_cur INTO @schema_name, @trigger_name, @object_name ; END GO -- Verify triggers are disabled. Should return an empty result set. SELECT * FROM sys.triggers WHERE is_disabled = 0 ; GO CLOSE trig_cur ; DEALLOCATE trig_cur;  

 

关于AFTER触发器(这里的AFTER包含了 after 和 for触发器)--只能在表上建
 1.触发器有个特性：它是按语句触发 而不是按行 所以你插入语句是多行的时候 只触发一次触发器而已.
   我们可以用@@identity 在触发器内判断受影响的记录数 然后对应进行操作（当然还有适合任何类型的内部SQL语句 无论它的影响条数是多少）
   SET NOCOUNT ON; USE tempdb; GO IF OBJECT_ID('dbo.T1') IS NOT NULL DROP TABLE dbo.T1; GO CREATE TABLE dbo.T1 ( keycol INT NOT NULL PRIMARY KEY, datacol VARCHAR(10) NOT NULL ); GO CREATE TRIGGER trg_T1_i ON T1 FOR INSERT AS DECLARE @rc AS INT; SET @rc = @@rowcount; IF @rc = 0 RETURN; --如果受影响是0行 直接返回 DECLARE @keycol AS INT, @datacol AS VARCHAR(10); IF @rc = 1 --如果受影响是1行 直接从表里赋值变量进行返回 BEGIN SELECT @keycol = keycol, @datacol = datacol FROM inserted; PRINT 'Handling keycol: ' + CAST(@keycol AS VARCHAR(10)) + ', datacol: ' + @datacol; END ELSE -- --如果受影响是多行行 使用循环进行逐行处理 BEGIN SELECT * INTO #I FROM inserted; CREATE UNIQUE CLUSTERED INDEX idx_keycol ON #I(keycol); --这里有涉及到一个技巧 为了能避免表扫描 将inserted表的数据转移到临时表 再给临时表加索引 SELECT @keycol = keycol, @datacol = datacol FROM (SELECT TOP (1) keycol, datacol FROM #I ORDER BY keycol) AS D; WHILE @@rowcount > 0 BEGIN PRINT 'Handling keycol: ' + CAST(@keycol AS VARCHAR(10)) + ', datacol: ' + @datacol; SELECT @keycol = keycol, @datacol = datacol FROM (SELECT TOP (1) keycol, datacol FROM #I WHERE keycol > @keycol ORDER BY keycol) AS D; END END GO --测试 INSERT INTO dbo.T1 SELECT 1, 'A' WHERE 1 = 0; GO INSERT INTO dbo.T1 SELECT 1, 'A'; GO INSERT INTO dbo.T1 SELECT 2, 'B' UNION ALL SELECT 3, 'C' UNION ALL SELECT 4, 'D'; GO  

 

2.避免触发器的发动(注意：不是禁用)
  这里所谓的避免,是指特殊时候特殊地点如果你在执行某个触发操作时候 你不想触发触发器 等你这个操作完了 以后的类似操作类型就要正常激发触发器
  这里提供一个方法:在触发器内部用一个判断条件来判断是否要触发(这里是用某个临时表的存在性)
  USE tempdb; GO IF OBJECT_ID('dbo.T1') IS NOT NULL DROP TABLE dbo.T1; GO CREATE TABLE dbo.T1(col1 INT); GO -- 创建触发器 CREATE TRIGGER trg_T1_i ON dbo.T1 FOR INSERT AS --判断 如果存在tempdb..#do_not_fire_trg_T1_i临时表 那么 回滚操作 IF OBJECT_ID('tempdb..#do_not_fire_trg_T1_i') IS NOT NULL rollback; --如果不存在 插入成功 else print 'success' ; GO --不使用临时表限定条件 插入成功 INSERT INTO dbo.T1 VALUES(1);------success GO --使用临时表 CREATE TABLE #do_not_fire_trg_T1_i(col1 INT); INSERT INTO T1 VALUES(2);--插入失败 -- C切忌要删除你建的临时表保证你的触发器能正常工作 DROP TABLE #do_not_fire_trg_T1_i; GO 

 

 3.指示表或视图中插入或更新了哪些列
  如果是单列 使用 update();
  如果是多列 使用 columns_update();具体参考MSDN
  这里给个公式 判断受影响的列
    IF (SUBSTRING(COLUMNS_UPDATED(),(@i - 1) / 8 + 1, 1)) & POWER(2, (@i - 1) % 8) > 0 则@i列受影响
 
关于INSTEAD OF 触发器--还可以在视图上建
 可以为视图或表定义 INSTEAD OF INSERT 触发器来替换 INSERT,delete ,udapet  语句的标准操作。
 这里注意 如果你的NSTEAD OF 触发器里面又再次触发引起INSTEAD OF 触发器的操作 则sql是不会再次使用INSTEAD OF 触发器的。即不可递归使用INSTEAD OF 触发器
 其实INSTEAD OF 触发器对于视图的操作是非常强大的。
 1.有时候如果你想同时为几个表添加数据,而且用一个SQL语句实现，这个时候可以用视图+INSTEAD OF 触发器实现
   create table t1 (id int ,value1 int) create table t2 (id int, value2 int) insert t1 values (1,2) insert t1 values (2,3) insert t2 values (1,4) insert t2 values (2,6) go --创建视图 create view t1_2 as select t1.id as id1 ,t1.value1,t2.id as id2,t2.value2 from t1 , t2 go --在视图上创建触发器 create trigger vie_tr on t1_2 instead of insert as insert t1 select id1,value1 from inserted insert t2 select id2,value1 from inserted --插入视图测试 insert t1_2 values(3,2,3,4) insert t1_2 values(3,2,4,4) select * from t1 select * from t2 --删除测试表 视图 drop table t1,t2 drop view t1_2  

 

 2.INSTEAD OF 触发器可以帮助你update原本不支持UPDATE的视图支持UPDATE
   比如你想更新视图的一些计算列 标志列等列 你就可以通过INSTEAD OF 触发器实现
   USE tempdb; GO IF OBJECT_ID('dbo.VOrderTotals') IS NOT NULL DROP VIEW dbo.VOrderTotals; GO IF OBJECT_ID('dbo.OrderDetails') IS NOT NULL DROP TABLE dbo.OrderDetails; GO CREATE TABLE dbo.OrderDetails ( oid INT NOT NULL, pid INT NOT NULL, qty INT NOT NULL, PRIMARY KEY(oid, pid) ); INSERT INTO dbo.OrderDetails(oid, pid, qty) VALUES(10248, 1, 10); INSERT INTO dbo.OrderDetails(oid, pid, qty) VALUES(10248, 2, 20); INSERT INTO dbo.OrderDetails(oid, pid, qty) VALUES(10248, 3, 30); INSERT INTO dbo.OrderDetails(oid, pid, qty) VALUES(10249, 1, 5); INSERT INTO dbo.OrderDetails(oid, pid, qty) VALUES(10249, 2, 10); INSERT INTO dbo.OrderDetails(oid, pid, qty) VALUES(10249, 3, 15); INSERT INTO dbo.OrderDetails(oid, pid, qty) VALUES(10250, 1, 20); INSERT INTO dbo.OrderDetails(oid, pid, qty) VALUES(10250, 2, 20); INSERT INTO dbo.OrderDetails(oid, pid, qty) VALUES(10250, 3, 20); GO --创建视图 CREATE VIEW dbo.VOrderTotals AS SELECT oid, SUM(qty) AS totalqty FROM dbo.OrderDetails GROUP BY oid; GO --在视图上创建触发器 CREATE TRIGGER trg_VOrderTotals_ioi ON dbo.VOrderTotals INSTEAD OF UPDATE AS IF @@rowcount = 0 RETURN; --如果更新的是 oid列 直接返回错误 IF UPDATE(oid) BEGIN RAISERROR('Updates to the OrderID column are not allowed.', 16, 1); ROLLBACK TRAN; RETURN; END; --如果是更新了 totalqty列 则利用CTE inserted deleted表将基础表的数据根据视图比例进行修改 WITH UPD_CTE AS ( SELECT qty, ROUND(1.*OD.qty / D.totalqty * I.totalqty, 0) AS newqty --这个是新列的值 FROM dbo.OrderDetails AS OD JOIN inserted AS I ON OD.oid = I.oid JOIN deleted AS D ON I.oid = D.oid ) UPDATE UPD_CTE SET qty = newqty; GO --测试数据 SELECT oid, pid, qty FROM dbo.OrderDetails; SELECT oid, totalqty FROM dbo.VOrderTotals; /* oid pid qty ----------- ----------- ----------- 10248 1 10 10248 2 20 10248 3 30 10249 1 5 10249 2 10 10249 3 15 10250 1 20 10250 2 20 10250 3 20 oid totalqty ----------- ----------- 10248 60 10249 30 10250 60 */ --进行更新视图操作 触发触发器 UPDATE dbo.VOrderTotals SET totalqty = totalqty * 2; --再次测试数据 SELECT oid, pid, qty FROM dbo.OrderDetails; SELECT oid, totalqty FROM dbo.VOrderTotals; /* oid pid qty ----------- ----------- ----------- 10248 1 20 10248 2 40 10248 3 60 10249 1 10 10249 2 20 10249 3 30 10250 1 40 10250 2 40 10250 3 40 oid totalqty ----------- ----------- 10248 120 10249 60 10250 120 */   

 3.INSTEAD OF 触发器还可以帮助你实现自增列的功能

  USE tempdb; GO IF OBJECT_ID('dbo.T1') IS NOT NULL DROP TABLE dbo.T1; GO CREATE TABLE dbo.T1 ( keycol INT NOT NULL PRIMARY KEY, datacol VARCHAR(10) NOT NULL ); GO --创建Sequence表来保留最新的列值 IF OBJECT_ID('dbo.Sequence') IS NOT NULL DROP TABLE dbo.Sequence; GO CREATE TABLE dbo.Sequence(val INT NOT NULL); INSERT INTO dbo.Sequence VALUES(0); GO --建立触发器 CREATE TRIGGER trg_T1_ioi_assign_key ON dbo.T1 INSTEAD OF INSERT AS DECLARE @rc AS INT, @key AS INT; SET @rc = @@rowcount; --如果是0行 则返回 IF @rc = 0 RETURN; --更新你的Sequence表 @key存储Sequence表的值,记住还要把Sequence表的值更新到最新的列值 UPDATE dbo.Sequence SET @key = val, val = val + @rc; --插入T1表 更新的列值 INSERT INTO dbo.T1(keycol, datacol) SELECT @key + ROW_NUMBER() OVER(ORDER BY getdate()), datacol FROM (SELECT datacol FROM inserted) AS I; GO --插入测试 INSERT INTO dbo.T1(datacol) SELECT LastName FROM Northwind.dbo.Employees; SELECT keycol, datacol FROM dbo.T1; /* keycol datacol ----------- ---------- 1 Buchanan 2 Callahan 3 Davolio 4 Dodsworth 5 Fuller 6 King 7 Leverling 8 Peacock 9 Suyama*/

 

再来说说DDL触发器
 前面的DML触发器是表级触发器,那DDL触发器就是数据库服务器级的触发器。
 它在响应数据定义语言 (DDL) 语句时触发。它们可以用于在数据库中执行管理任务，例如以关键字 CREATE、ALTER 和 DROP 开头的 Transact-SQL 语句对应的事件
 DDL 触发器可用于管理任务，例如审核和控制数据库操作。
 如果要执行以下操作，请使用 DDL 触发器：(MSDN)
  1.要防止对数据库架构进行某些更改。
  2.希望数据库中发生某种情况以响应数据库架构中的更改。
  3.要记录数据库架构中的更改或事件。
  4.仅在运行触发 DDL 触发器的 DDL 语句后，DDL 触发器才会激发。DDL 触发器无法作为 INSTEAD OF 触发器使用。

  a.下面的示例显示如何使用 DDL 触发器阻止修改或删除数据库中的任何表。
   CREATE TRIGGER safety ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT 'You must disable Trigger "safety" to drop or alter tables!' ROLLBACK ;  

b.如果当前服务器实例上发生任何 CREATE_DATABASE 事件，DDL 触发器将输出消息
IF EXISTS (SELECT * FROM sys.server_triggers WHERE name = 'ddl_trig_database') DROP TRIGGER ddl_trig_database ON ALL SERVER; GO CREATE TRIGGER ddl_trig_database ON ALL SERVER FOR CREATE_DATABASE AS PRINT 'Database Created.' SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') GO 
    ps:DDL触发器就到这 其实内容很多 请参考MSDN
 
最后的登入触发器：不太了解 MSDN上有详细介绍 请参阅MSDN