[推荐] 【T-MAC学习笔记19之--浅谈触发器】 88feixianxxx]

发布时间:2016-12-6 8:57:09 编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"[推荐] 【T-MAC学习笔记19之--浅谈触发器】 88feixianxxx]",主要涉及到[推荐] 【T-MAC学习笔记19之--浅谈触发器】 88feixianxxx]方面的内容,对于[推荐] 【T-MAC学习笔记19之--浅谈触发器】 88feixianxxx]感兴趣的同学可以参考一下。

本帖最后由 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*/ 触发器中的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

sf

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 [email protected] 关于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

SF

学习

NND  排版太麻烦了。。。

引用 5 楼 feixianxxx 的回复:NND 排版太麻烦了。。。 你自己不就可以排吗?

引用 6 楼 fredrickhu 的回复:引用 5 楼 feixianxxx 的回复: NND 排版太麻烦了。。。 你自己不就可以排吗? 自己排了 部署不好。。。

HAHA 谁有SD卡 SD模式的源代码 谢谢

。。。。。。。。。。。。

下次也这样多散点分

该回复于2010-07-28 09:32:49被版主删除

         UP    

什么是触发器啊,不懂

学习 收藏

saaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

d xiexie 

up

.

我来学习下了

引用 19 楼 tr0j4n 的回复:我来学习下了 某某板 版主

一小撮不明真相的烤鸭路过

路过学习下

路过学习下 A 

e还没写过触发器,学习学习。

继续学习中……

感叹!

感慨,自叹不如!!!

引用 20 楼 feixianxxx 的回复:引用 19 楼 tr0j4n 的回复: 我来学习下了 某某板 版主 玩C++的 是.net版的

看不太懂哦!

恩 有感而发

学习。

学习了,看看了~~

顶起.

程序真不错

学习

dingdign

up

你自己写的?泼妇?

学习

长见识了 呵呵

当涉及外表的一些规则时候 需要在本表里使用trigger

学习一下

学习。。。

ding

谢谢分享!!

收益匪浅。

你把电子版书发给大家,让大家下载不就行了,搞这些连载

怎么搞得?不感兴趣!

学习。

  学习.学习

  学习.学习

学习了!

引用 47 楼 xeqtrl982 的回复:你把电子版书发给大家,让大家下载不就行了,搞这些连载没有中文版  而且 不全是

不太懂,先收藏

学习,收藏

学习

学习了

学习了

来膜拜楼主的..顺便学习

领教了,收藏,O(∩_∩)O~

先顶再看!!!! 收藏

学习了,感谢分享!

学习!

ding

谢谢分享

好强大的帖子啊

88

学习!

上一篇:用SQL2005的SSIS,如何做FTP??? 100viva369]
下一篇:这种需求怎么写MDX语句?命计算成员? 40分,无满意结帖,结帖人zxz719]

相关文章

相关评论