SQL Server建立觸發器

標籤：style   blog   http   io   color   ar   使用   sp   for   

為什麼需要觸發器為什麼需要觸發器(TRIGGER)呢？典型的應用就是銀行的取款機系統 假定該系統的資料庫設計需要兩張表：帳戶資訊表(bank)存放帳戶的資訊，交易資訊表(transInfo)存放每次的交易資訊。 上述結果有什麼錯誤？您一定發現了，當張三取錢200元時，雖然交易資訊表(transInfo)中儲存了取錢200元的交易資訊，但帳戶表(bank)中的餘額仍是1000，沒有自動跟隨修改。顯然，我們應該根據交易類型是“支取”還是“存入”，自動減少或增加帳戶表中的餘額。而且，它還應該具有事務的特徵：一旦交易失敗，餘額修改也應該自動取消 。那麼，如何解決呢？這種特殊的商務規則使用普通約束行嗎 ？答案顯然是否定的 。使用事務行嗎？事務能保證一旦交易失敗，餘額修改也自動取消。但實現不了自動修改的觸發功能 。所以，最優的解決方案就是採用觸發器。觸發器它是一種特殊的預存程序，並且也具有事務的功能，它能在多表之間執行特殊的商務規則或保持複雜的資料邏輯關係 。 什麼是觸發器再看一個例子：目前有兩張表，分別存放在職員工和退休員工的資訊。假定現趙二退休：趙二將從員工表中刪除。一旦刪除趙二的資訊，應自動觸發一個動作：將趙二的資訊儲存到退休員工表中。強調：自動觸發，而不是手動，如何?呢，當然是使用觸發器。q       觸發器是在對錶進行插入、更新或刪除操作時自動執行的預存程序q       觸發器通常用於強制商務規則q       觸發器是一種進階約束，可以定義比用CHECK 條件約束更為複雜的約束q       可執行複雜的SQL語句（if/while/case）q       可引用其它表中的列q       觸發器定義在特定的表上，與表相關q       自動觸發執行q       不像預存程序，不需要也不能人工調用執行q       本身就是一個事務，所以，如果發現有錯誤，可以復原撤銷操作。 觸發器的類型INSERT觸發器：當向表中插入資料時觸發，自動執行觸發器所定義的SQL語句。UPDATE觸發器：當更新表中某列多列時觸發，自動執行觸發器所定義的SQL語句。DELETE觸發器：當刪除表中記錄時觸發，自動執行觸發器所定義的SQL語句。   inserted 和deleted 表q       觸發器觸發時：q       系統自動在記憶體中建立deleted表或inserted表q       唯讀，不允許修改；觸發器執行完成後，自動刪除q       inserted 表q       臨時儲存了插入或更新後的記錄行q       可以從inserted表中檢查插入的資料是否滿足業務需求q         如果不滿足，則向使用者報告錯誤訊息，並復原插入操作q       deleted 表q       臨時儲存了刪除或更新前的記錄行q       可以從deleted表中檢查被刪除的資料是否滿足業務需求q         如果不滿足，則向使用者報告錯誤訊息，並復原插入操作修改操作inserted表deleted表增加(INSERT)記錄存放新增的記錄------刪除(DELETE)記錄-----存放被刪除的記錄修改(UPDATE)記錄存放更新後的記錄存放更新前的記錄inserted表和deleted表存放的資訊 如何建立觸發器建立觸發器的文法：CREATE TRIGGER trigger_name ON table_name [WITH ENCRYPTION]  FOR [DELETE, INSERT, UPDATE] AS  T-SQL語句GOWITH ENCRYPTION表示加密觸發器定義的SQL文本DELETE, INSERT, UPDATE指定觸發器的類型insert觸發器的工作原理：1.執行insert插入語句，在表中插入資料行；2.觸發insert觸發器，向系統暫存資料表inserted表中插入新行的備份（副本）3.觸發器檢查inserted表中插入的新行資料，確定是否需要復原或執行其他動作。問題：解決上述的銀行取款問題：當向交易資訊表(transInfo)中插入一條交易資訊時，我們應自動更新對應帳戶的餘額。 分析：l        在交易資訊表上建立INSERT觸發器l        從inserted暫存資料表中擷取插入的資料行l        根據交易類型(transType)欄位的值是存入/支取，l         增加/減少對應帳戶的餘額。CREATE TRIGGER trig_transInfo ON transInfo  FOR INSERT   AS   DECLARE @type char(4),@outMoney MONEY   DECLARE @myCardID char(10),@balance MONEY   SELECT @type=transType,@outMoney=transMoney，         @myCardID=cardID FROM inserted /*從inserted表中擷取交易類型、交易金額等*/     IF (@type=‘支取‘) /*根據交易類型，減少或增加對應卡號的餘額 */        UPDATE bank SET currentMoney=currentMoney-@outMoney             WHERE cardID=@myCardID    ELSE        UPDATE bank SET currentMoney=currentMoney+@outMoney             WHERE cardID=@myCardID    …..GO delete觸發器的工作原理：1.執行delete刪除語句，刪除表中的資料行；2.觸發delete刪除觸發器，向系統暫存資料表的deleted表中插入被刪除的副本3.觸發器檢查deleted表中被刪除的資料，確定是否需要復原或執行其他動作。問題：當刪除交易資訊表時，要求自動備份被刪除的資料到表backupTable中 。 分析：l        在交易資訊表上建立DELETE觸發器l        被刪除的資料可以從deleted表中擷取CREATE TRIGGER trig_delete_transInfo ON transInfo  FOR DELETE   AS      print ‘開始備份資料，請稍後......‘      IF NOT EXISTS(SELECT * FROM sysobjects           WHERE name=‘backupTable‘)         SELECT * INTO backupTable FROM deleted/*從deleted表中擷取被刪除的交易記錄*/     ELSE         INSERT INTO backupTable SELECT * FROM deleted      print ‘備份資料成功，備份表中的資料為:‘      SELECT * FROM backupTableGO UPDATE觸發器的工作原理：執行更新操作，例如把李四的餘額改為20001元。更新操作可以看出兩步：1.刪除李四原有的資料：李四 1000 0002  1，將資料備份到deleted表中。2.再插入新行：李四 1000 0002  20001，將資料備份到inserted表中。最後看起來就是把餘額從1元修改為20001元了。所以：如果我們希望查看修改前的未經處理資料，可以查看錶deleted 。如果我們希望查看修改後的資料，可以查看錶inserted 。問題：跟蹤使用者的交易，交易金額超過20000元，則取消交易，並給出錯誤提示。 分析：l        在bank表上建立UPDATE觸發器l        修改前的資料可以從deleted表中擷取l        修改後的資料可以從inserted表中擷取CREATE TRIGGER trig_update_bank ON bank  FOR UPDATE   AS      DECLARE @beforeMoney MONEY,@afterMoney MONEY /*從deleted表中擷取交易前的餘額，從inserted表中擷取交易後的餘額*/      SELECT @beforeMoney=currentMoney FROM deleted        SELECT @afterMoney=currentMoney FROM inserted         IF ABS(@afterMoney-@beforeMoney)>20000 /*交易金額是否>2萬*/        BEGIN            print ‘交易金額:‘+convert(varchar(8),                ABS(@afterMoney-@beforeMoney))            RAISERROR (‘每筆交易不能超過2萬元，交易失敗‘,16,1)            ROLLBACK TRANSACTION /*復原事務，撤銷交易*/         ENDGO 列級 UPDATE 觸發器q       UPDATE觸發器除了跟蹤資料的變化（修改）外，還可以檢查是否修改了某列的資料q       使用UPDATE（）（列）函數檢測是否修改了某列問題：交易日期一般由系統自動產生，預設為當前日期。為了安全起見，一般禁止修改，以防舞弊。 分析：UPDATE（列名）函數可以檢測是否修改了某列CREATE TRIGGER trig_update_transInfo ON transInfo  FOR UPDATE   AS      IF UPDATE(transDate)/* 檢查是否修改了交易日期列transDate*/         BEGIN            print ‘交易失敗.....‘            RAISERROR (‘安全警告：交易日期不能修改，                           由系統自動產生‘,16,1)            ROLLBACK TRANSACTION   /*復原事務，撤銷交易*/         ENDGO 好，我們來做個總結：q       觸發器是在對錶進行插入、更新或刪除操作時自動執行的預存程序，觸發器通常用於強制商務規則q       觸發器還是一個特殊的事務單元，當出現錯誤時，可以執行ROLLBACK TRANSACTION復原撤銷操作q       觸發器一般都需要使用暫存資料表：deleted表和inserted表，它們存放了被刪除或插入的記錄行副本q       觸發器類型：q        INSERT觸發器q        UPDATE觸發器q        DELETE觸發器

 

SQL Server建立觸發器