觸發器捕獲批量修改

　　最近在批量更新數據時，發現無論怎麼操作觸發器都僅僅捕獲到了一條數據，觸發器代碼大致如下。

ALTER TRIGGER [dbo].[Trigger_Demo_Update] 
   ON  [dbo].[B_Demo_TB] 
   AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;
    DECLARE @NewData INT,@OldData INT;

    SELECT @OldData=Data FROM DELETED

    SELECT @NewData=Data FROM INSERTED

        IF(@OldData!=@NewData)
        BEGIN
            INSERT INTO M_TriggleSoft(Data)
            VALUES(@NewData)
        END
        
END

        我們知道在觸發器中DELETE和INSERTED其實是兩張虛表，因此用變數獲取僅能得到一條數據，但是如果用游標的話可以獲取到位於虛表中的所有數據，批量處理。於是修改如下：

ALTER TRIGGER [dbo].[Trigger_Demo_Update] 
   ON  [dbo].[B_Demo_TB] 
   AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;
    DECLARE @NewData INT,@OldData INT;

    DECLARE cursorDel CURSOR FOR SELECT Data FROM DELETED

    DECLARE cursorINSERT CURSOR FOR SELECT Data FROM INSERTED

    open cursorDel
    open cursorINSERT

    FETCH NEXT FROM cursorDel INTO @OldData

    FETCH NEXT FROM cursorINSERT INTO @NewData
    

    WHILE @@FETCH_STATUS=0
        BEGIN
            IF(@OldData!=@NewData)
            BEGIN
                INSERT INTO M_TriggleSoft(Data)
                VALUES(@NewData)
            END
            FETCH NEXT FROM cursorDel INTO @OldData

            FETCH NEXT FROM cursorINSERT INTO @NewData
         END
    CLOSE cursorDel
    DEALLOCATE cursorDel
    CLOSE cursorINSERT
    DEALLOCATE cursorINSERT
END

       既然明白了觸發器中DELETE和INSERTED是兩張虛表，那麼其實上述的操作也可以這麼來:

ALTER TRIGGER [dbo].[Trigger_Demo_Update] 
   ON  [dbo].[B_Demo_TB] 
   AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;
    DECLARE @NewData INT,@OldData INT;

    SELECT @OldData=Data FROM DELETED

    SELECT @NewData=Data FROM INSERTED

            INSERT INTO M_TriggleSoft(DATA) SELECT Data FROM INSERTED INNER JOIN DELETED ON INSERTED.ID=DELETED.ID WHERE INSERTED.DATA=DELETED.DATA
        END
        
END