觸發器之刪除某列欄位自動備份

觸發器之刪除某列欄位自動備份    作者：企業庫    時間：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表儲存了被刪除行或插入的記錄行的副本。我們可以從這兩個表中查詢出變化前的值並賦給變數。)