14第十四章触发器(转载)

14第十四章触发器

 

DML --> AFTER / FOR   UPDATE , INSERT , DELETE  -- 用来级联删除

         --> INSTEAD OF  在 时间之前触发，相当于 bef

INSERTED, DELETED 两张表要好好利用。

在创建 DML 触发器时，不能使用下列语句：

CREATE / ALTER /DROP DATABASE

LOAD DATABASE / LOAD LOG / RECONFIGURE

RESTORE DATABASE  / RESTORE LOG

自动事务处理模式下，还是在隐式或显示事务处理模式下，只要在 触发器中发出 BEGIN TRANSACTION 语句，实际上就开始了一个嵌套事务，当触发器中使用 ROLLBACK TRANSACTION 语句回滚嵌套事务时，触发器本身发出的所有的 BEGIN TRANSACTION 语句回滚嵌套事务时，触发器本身发出的额所有 BEGIN TRANSACTION 语句豆浆被忽略， ROLLBACK 将回滚到最外部的 BEGIN TRANSACTION 。而在 这 最外部的 之前的 事务都已经提交的就不会收到影响，

而这个 回滚操作也会终止 批处理中 对 该语句后面语句的执行。

因此，若要在 触发器中进行部分回滚，应当使用 SAVE TRANSACTION 语句设置一个事务保存点，这样就不会回滚到 外部的 事务中去了。

注意：  在 触发器中 书写 COMMIT TRANSACTION 的 语句，如果之前有 BEGIN TRANSACTION 语句，会被认为是

仅提交该嵌套事务，如果 在 commit 之后仍然有 ROLLBACK TRANSACTION 那么仍然会回滚到最外部的 事务。

if (update(name)) 

 

用来判断 更新的是哪列， COLUMNS_UPDATED() 测试多个列， 但这个列 是 按字节  加起来算的，这个函数返回一个

或多个从左至右排序的字节。 P346 是一个很经典的应用。

指定 FIRST 触发器 和 LAST 触发器

FIRST 和 LAST 触发器之间的执行并没有先后顺序：

sp_settriggerorder 

@triggername ='ud_trig/ins_trig/del_trig', @order = 'first/last', @stmttyp = 'update / insert / delete';

由于 INSTEAD OF 触发器一直在对基础表进行更新前激发，因此不能讲 INSTEAD OF 触发器指定为 第一或 最后 一个触发器

如果使用了 ALTER TRIGGER 语句 更改了 First 或 Last 触发器，则会删除它们的顺序值，必须使用 sp_settriggerorder 来重新设置。

可以通过 OBJECTPROPERTY()函数的ExecIsFirstDeleteTrigger , ExecIsFirstInsertTrigger,ExecIsFirstUPdate....等属性来确定触发器时 First 触发器，还是LAST 触发器。

嵌套和递归触发器

无论是 DML 触发器还是DDL 触发器，如果出现了一个触发器执行启动另一个触发器的操作都属于嵌套触发器。32层

可以通过nested triggers 服务器配置选项来空值是否可以嵌套AFTER 触发器。 INSTEAD OF 触发器嵌套不受此选项影响。参考下面的语句：

sp_configure 'nested triggers',1 -- 设置 为 1 允许 after 触发器嵌套

GO

RECONFIGURE; --使用新环境值

EXEC sp_configure 'nested triggers'; --查看 nested triggers 选项设置

GO

递归 P349  有个经典例子 由于有 update() 函数检测，作为递归终止条件。

递归分为 

直接递归， 如 应用程序更新 T3 表，从而触发了 触发器 Trig3 , Trig3 再次更新表T3，从而再次出发了触发器Trig3

间接递归。 即中间经过另外的表中转还是触发了第一张表的触发器：

应用程序更新了 表 T1， 从而触发了触发器Trig1 , Trig1 更新了表T2，从而出发了触发器 Trig2.Trig2转而更新了 表T1 ， 从而再次触发了 Trig1.

注意： 只有在设置 RECURSIVE_TRIGGERS 数据库选项为 ON 的情况下，才允许以递归方式调用AFTER 触发器：

ALTER DATABASE AdventureWorks 

SET RECURSIVE_TRIGGERS ON;

Instead of 触发器：

-- instead of insert

CREATE TRIGGER Bef_Ins 

ON dbo.test11 

INSTEAD OF INSERT

AS

IF (exists(select * from dbo.Test11 where name = (select name from inserted)))

print 'exists already!!!'

else

insert into dbo.test11

select name,gender from inserted

-- instead of update 同理

instead of 给 insert 跟 update 都必须为不能为空的列指定值，但是在触发器中需要忽略掉这些值。

3. 在INSTEAD OF 触发器中使用 TEXT, NTEXT 和 IMAGE 数据

数据修改可能会涉及 text/ ntext /image 列。 在基表中， 存储在 text/ ntext 或 image 列中 的 值是文本指针，它只想保存数据的 页  P353

IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[INS_TEST]'))

DROP TRIGGER [dbo].[INS_TEST]

GO

CREATE TRIGGER ins_Stu

ON dbo.Students

AFTER INSERT

AS

SELECT * FROM INSERTED

insert into dbo.Students

values (4,'Frank',88.88)

CREATE TRIGGER del_Stu 

ON dbo.Students

FOR DELETE

AS

SELECT * FROM DELETED

DELETE FROM dbo.Students WHERE StudentName = 'Frank'

CREATE TRIGGER update_Stu 

ON dbo.Students

FOR UPDATE

AS

SELECT * FROM INSERTED

SELECT * FROM DELETED

insert into dbo.Students

VALUES(4,'Frank',88.88)

update dbo.Students set ClassID=5 where StudentName='Frank'

DELETE FROM dbo.Students WHERE StudentName = 'Frank'

------------- DDL 触发器 ----------------------------

DDL 触发器是为相应一个或多个特定的数据定义语言语句的激发。并且 DDL 触发器只能在 SQL 语句完成之后才运行，无法作为 INSTEAD OF 触发器。

sys.server_triggers 目录视图查询服务器范围内的 DDL 触发器的信息。P354

修改，删除和禁用触发器

DROP TRIGGER MyTrigger

ALTER TRIGGER MyTRIGGER

ON PriTable

AFTER DELETE 

AS

    DELETE FROM DetailTable

    WHERE OrderID in (SELECT OrderID from Deleted);

--禁用 触发器  方法一

Disable trigger dbo.PriTrigger ON dbo.PriTable; -- DDL 触发器的话不能包含架构名

--禁用 触发器  方法二

ALTER TABLE dbo.PriTable

    DISABLE TRIGGER PriTrigger;

--重新启用 触发器 方法一：

ENABLE TRIGGER dbo.PriTrigger ON dbo.PriTable

--启用触发器：

ALTER TABLE dbo.PriTable

        ENABLE TRIGGER PriTrigger;

注意要删除一个 DDL 触发器，需要制定触发器的作用域范围，否则将默认为要删除DML 触发器。

DROP TRIGGER MyTrigger

ON DATABASE;

GO

DROP TRIGGER mYoTHERtRIGGER

on all server;

禁用启用也同样要指定作用域范围：

DISABLE TRIGGER Safety

ON DATABASE;

ENABLE TRIGGER Safety 

ON DATABASE;

要修改一个 DDL 触发器，也应当使用 ALTER TRIGGER 语句。 例如：

ALTER TRIGGER Safety

ON DATABASE 

FOR CREATE_TABLE

AS

    PRINT N'CREATE TABLE 出错';

    SELECT 

EVENTDATA().VALUE('(/EVENT_INSTANCE/TSQLCommand/cOMMANDtEXT)[1]','nvarchar(max)');

ROLLBACK;

原文链接

本文由豆约翰博客备份专家远程一键发布