Trigger Capture Batch Modification

Recently, when the data was updated in bulk, it was discovered that no matter how the trigger was captured just one piece of data, the trigger code was roughly the following.

ALTER TRIGGER [dbo].[trigger_demo_update]     on  [dbo].[B_DEMO_TB] AfterUPDATE as BEGIN    SETNOCOUNT on; DECLARE @NewData INT,@OldData INT; SELECT @OldData=Data fromDELETEDSELECT @NewData=Data fromINSERTEDIF(@OldData!=@NewData)        BEGIN            INSERT  intoM_trigglesoft (Data)VALUES(@NewData)        END        END

We know that in triggers, delete and inserted are actually two virtual tables, so a variable gets only one piece of data, but if you use a cursor, you can get all the data in the virtual table and batch it. The changes are as follows:

ALTER TRIGGER [dbo].[trigger_demo_update]     on  [dbo].[B_DEMO_TB] AfterUPDATE as BEGIN    SETNOCOUNT on; DECLARE @NewData INT,@OldData INT; DECLARECursordelCURSOR  for SELECTData fromDELETEDDECLARECursorinsertCURSOR  for SELECTData fromINSERTEDOpenCursordelOpenCursorinsertFETCH NEXT  fromCursordel into @OldData    FETCH NEXT  fromCursorinsert into @NewData         while @ @FETCH_STATUS=0        BEGIN            IF(@OldData!=@NewData)            BEGIN                INSERT  intoM_trigglesoft (Data)VALUES(@NewData)            END            FETCH NEXT  fromCursordel into @OldData            FETCH NEXT  fromCursorinsert into @NewData         END    CLOSECursordeldeallocateCursordelCLOSECursorinsertdeallocateCursorinsertEND

Now that you understand that a trigger has two virtual tables, delete and inserted, you can actually do the following:

ALTER TRIGGER [dbo].[trigger_demo_update]     on  [dbo].[B_DEMO_TB] AfterUPDATE as BEGIN    SETNOCOUNT on; DECLARE @NewData INT,@OldData INT; SELECT @OldData=Data fromDELETEDSELECT @NewData=Data fromINSERTEDINSERT  intoM_trigglesoft (DATA)SELECTData fromINSERTEDINNER JOINDELETED onInserted.id=Deleted.idWHEREINSERTED. DATA=DELETED. DATAEND        END

Trigger Capture Batch Modification