MS SQL Trigger Example

MS SQL Trigger Demo

使用時機

資料表異動時，想記錄異動的資料列。參考 [1] 有很棒的說明。

範例

已知 :

有一個資料表 [MyUser]，你想要記錄該資料表的異動狀態，並將該異動狀態記錄在 [MyUser_Log] 資料表中

[MyUser] 資料表設計如下

CREATE TABLE [dbo].[MyUser](
    [SN] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL, 
 CONSTRAINT [PK_MyUser] PRIMARY KEY CLUSTERED 
(
    [SN] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

解題

Step 1. 建立 [MyUser_log] 資料表，記錄異動狀態，包含異動狀態又時間，設計如下

CREATE TABLE [dbo].[MyUser_Log](
    [SN] [int],
    [Name] [nvarchar](50),  
    [ST] [nvarchar](50),
    [CreatedDate] [datetime]
    )

其中，CreateDate 記錄異動的時間，ST 記錄異動狀態

Step 2. 建立 [MyUser] 資料表的 Trigger, 如下

CREATE Trigger [dbo].[MyUser_I_U_D] ON [dbo].[MyUser] AFTER UPDATE, INSERT, DELETE
AS
BEGIN
    --INSERT
    IF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted)
    BEGIN
        Insert Into MyUser_Log Select *, N'After Inserted', getdate() From Inserted
    END   

    -- UPDATE
IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
BEGIN
        Insert Into MyUser_Log Select *, N'Before Update', getdate() From deleted
        Insert Into MyUser_Log Select *, N'After Updated', getdate() From Inserted      
    END   

    --DELETE 
   IF NOT EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
   BEGIN
    Insert Into MyUser_Log Select *, N'Before Deleted', getdate() From deleted
   END
END

測試

新增

--新增一筆資料
  INSERT INTO MyUser(Name)VALUES(N'J')
  SELECT * FROM MyUser_Log

修改

  --更改一筆資料
  UPDATE MyUser SET Name = N'M'
  SELECT * FROM MyUser_Log

刪除

  --刪除一筆資料
  DELETE MyUser
  SELECT * FROM MyUser_Log

參考資料介紹

[1] 說明 Trigger 用途，介紹 Trigger 運作原理，Trigger 使用範例 (資料儲存使用 XML) 及 XML 查詢方式

[2] 說明 Trigger 用途, Trigger 範例, Step by Step (圖文並茂)

[3] MSDN Trigger 語法

參考資料

[1] 軟體開發的天空-使用 Trigger 紀錄資料表的新增、修改、刪除的行為

[2] SQL Server，Trigger 的簡單範例，以「訂單的流程系統」為例

[3] MSDN-CREATE TRIGGER (Transact-SQL)