触发器捕获批量修改

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

        我们知道在触发器中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

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

END

本文由金沙官网线上发布于数据库,转载请注明出处:触发器捕获批量修改

您可能还会对下面的文章感兴趣: