【T-MAC学习笔记19之--浅谈触发器】
本帖最后由 feixianxxx 于 2009-12-03 20:41:33 编辑 很难排版 有些东西 没写在帖子里
请看原来的地址http://blog.csdn.net/feixianxxx/archive/2009/12/03/4935151.aspx
这次谈的是触发器
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触发器:在处理触发操作（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*/
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
--获取有关数据库中的触发器的信息
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;
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
