触发器之删除某行字段自动备份

触发器之删除某行字段自动备份    作者：企业库    时间：2010-4-8 10:52:07

起先开始写一个简单的触发器： create TRIGGER trigger_delete_TMaintain on TMaintain for delete AS BEGIN SET NOCOUNT ON; insert into TDeleteMaintainLog select * from deleted END GO （这边介绍一下： SET NOCOUNT ON的作用： 当 SET NOCOUNT 为 ON 时，不返回计数（表示受 Transact-SQL 语句影响的行数）。当 SET NOCOUNT 为 OFF 时，返回计数。 如果存储过程中包含的一些语句并不返回许多实际的数据，则该设置由于大量减少了网络流量，因此可显著提高性能。 ） 开始执行上面的触发器，问题来了。 提示错误： 消息 311，级别 16，状态 1，过程 trigger_delete_TMaintain，第 12 行 不能在 'inserted' 表和 'deleted' 表中使用 text、ntext 或 image 列。 问题出在：create TRIGGER trigger_delete_TMaintain on TMaintain for delete   不能用for，改为instead of （介绍一下： AFTER 指定触发器只有在触发 SQL 语句中指定的所有操作都已成功执行后才激发。所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器。 如果仅指定 FOR 关键字，则 AFTER 是默认设置。 不能在视图上定义 AFTER 触发器。 INSTEAD OF 指定执行触发器而不是执行触发 SQL 语句，从而替代触发语句的操作。 在表或视图上，每个 INSERT、UPDATE 或 DELETE 语句最多可以定义一个 INSTEAD OF 触发器。然而，可以在每个具有 INSTEAD OF 触发器的视图上定义视图。 INSTEAD OF 触发器不能在 WITH CHECK OPTION 的可更新视图上定义。如果向指定了 WITH CHECK OPTION 选项的可更新视图添加 INSTEAD OF 触发器，SQL Server 将产生一个错误。用户必须用 ALTER VIEW 删除该选项后才能定义 INSTEAD OF 触发器。 after在sql2005里没有，等同于for。   ） 还有个问题： 提示错误： 消息 213，级别 16，状态 1，过程 trigger_delete_THardRepair，第 12 行 插入错误: 列名或所提供值的数目与表定义不匹配。 因为删除表里面多了字段，导致错误。怎么办？ 很简单：insert into TDeleteHardRepairLog            ([HardRepairID]         。。。。            ,[MakerPhone]) select * from deleted（字段一个一个写出来） 问题解决了，执行一下，没有问题。接着我们去删除： 测试语句：（SELECT * from Tmaintain SELECT * from Tdeletemaintainlog delete from Tmaintain where MAINTAINID=1 delete from Tdeletemaintainlog where MAINTAINID=1） 执行delete from Tmaintain where MAINTAINID=1，问题又来了。 提示错误： 消息 544，级别 16，状态 1，过程 trigger_delete_TMaintain，第 12 行 当 IDENTITY_INSERT 设置为 OFF 时，不能为表 'TDeleteMaintainLog' 中的标识列插入显式值。 语句已终止。 应改为SET IDENTITY_INSERT 允许将显式值插入表的标识列中。就是off改成on。 在插入前后添加，SET IDENTITY_INSERT TDeleteMaintainLog ON; insert into。。。 SET IDENTITY_INSERT TDeleteMaintainLog OFF; 问题解决了，执行一下，没有问题。接着我们去删除： 还是没有被删除掉，但是有自动备份了。 怎么办？方法应该是没有错。 想可不可以在这个触发器里删除。 在插入后面添加： delete THardRepair where HardRepairID in (select HardRepairID from DELETED) 执行一下，没有问题。接着我们去删除： 可以了，删除了并且自动备份了。 大功告成。 代码如下： -- ================================================ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author:   lcq -- Create date:2010/4/7 -- Description: 创建TMaintain删除操作的触发器 -- ============================================= alter TRIGGER trigger_delete_TMaintain on TMaintain instead of delete AS BEGIN SET NOCOUNT ON; SET IDENTITY_INSERT TDeleteMaintainLog ON; insert into TDeleteMaintainLog            ([MaintainID]            ,[Question]            ,[Solution]            ,[OutTime]            ,[InPlaceTime]            ,[FinishedTime]            ,[PredictTime]            ,[Fee]            ,[Sign]            ,[Standby]            ,[ClientsID]            ,[Technician]            ,[SendPerson]            ,[Description]            ,[AcceptTime]            ,[ProductID]            ,[Number]            ,[Status]) select * from deleted delete TMaintain where MaintainID in (select MaintainID from DELETED) SET IDENTITY_INSERT TDeleteMaintainLog off; END GO (说明一下： SQL2000中，inserted表和deleted表用于存放对表中数据行的修改信息。他们是触发器执行时自动创建的，放在内存中，是临时表。当触发器工作完成，它们也被删除。它们是只读表，不能向它们写入内容。   inserted表：用来存储INSERT和UPDATE语句所影响的行的副本。意思就是在inserted表中临时保存了被插入或被更新后的记录行。在执行 INSERT 或UPDATE 语句时，新加行被同时添加到inserted表和触发器表中。因此，可以从inserted表检查插入的数据是否满足需求，如不满足则回滚撤消操(cāo)作。   deleted表：用来存储DELETE和UPDATE语句所影响行的副本。意思是在delete表中临时保存了被删除或被更新前的记录行。在执行 DELETE 或 UPDATE 语句时，行从触发器表中删除，并传到deleted表中。所以可以从deleted表中检查删除的数据行是否能删除。   所以当表中某条记录的某项值发生变化时，变化前的值已经通过系统自动创建的临时表deleted表和inserted表保存了被删除行或插入的记录行的副本。我们可以从这两个表中查询出变化前的值并赋给变量。)

转载于:https://www.cnblogs.com/heartstill/archive/2011/11/24/2261507.html