SQL Server Trigger to work with multiple row inserts

Question

I support code that has a trigger in a table to increase a column. This column is then used by third-party application A. Suppose the table is called a test with two columns num1 and num2. A trigger is fired on every num1 insert in the test. The following is the trigger:

USE [db1] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER TRIGGER [dbo].[TEST_MYTRIG] ON [dbo].[test] FOR INSERT AS begin SET NOCOUNT ON DECLARE @PROC_NEWNUM1 VARCHAR (10) DECLARE @NEWNUM2 numeric(20) SELECT @PROC_NEWNUM1 = num1 FROM INSERTED select @NEWNUM2 = MAX(num2) from TEST if @NEWNUM2 is null Begin set @NEWNUM2 = 0 end set @NEWNUM2 = @NEWNUM2 + 1 UPDATE TEST SET num2 = @NEWNUM2 WHERE num1 = @PROC_NEWNUM1 SET NOCOUNT OFF End 

This works fine in simple row-based inserts, but there is another third-party application B (sigh) that sometimes makes several inserts in this table like this, but not exactly:

 INSERT INTO [db1].[dbo].[test] ([num1]) Select db1.dbo.test.num1 from [db1].[dbo].[test] GO 

This triggers the trigger randomly ...

Now I do not have access to the application source A or B and manage only the database and the trigger. Is there anything that can be done with a trigger so that updates made to num2 are correct in the case of multiple inserts?

Decision:

The following is an affine code based solution:

  DECLARE @PROC_NEWNUM1 VARCHAR (10) DECLARE @NEWNUM2 numeric(20) DECLARE my_Cursor CURSOR FAST_FORWARD FOR SELECT num1 FROM INSERTED; OPEN my_Cursor FETCH NEXT FROM my_Cursor into @PROC_NEWNUM1 WHILE @@FETCH_STATUS = 0 BEGIN select @NEWNUM2 = MAX(num2) from TEST if @NEWNUM2 is null Begin set @NEWNUM2 = 0 End set @NEWNUM2 = @NEWNUM2 + 1 UPDATE TEST SET num2 = @NEWNUM2 WHERE num1 = @PROC_NEWNUM1 FETCH NEXT FROM my_Cursor into @PROC_NEWNUM1 END CLOSE my_Cursor DEALLOCATE my_Cursor 

Check out the set-based approach here: SQL Server - restart the trigger to avoid the cursor-based approach

Answer 1

You just need to open the cursor on INSERTED and repeat it for @ PROC_NEWNUM1 and put the rest of the code in this loop. eg

  DECLARE @PROC_NEWNUM1 VARCHAR (10) DECLARE @NEWNUM2 numeric(20) DECLARE my_Cursor CURSOR FOR SELECT num1 FROM INSERTED; OPEN my_Cursor; FETCH NEXT FROM @PROC_NEWNUM1; WHILE @@FETCH_STATUS = 0 BEGIN FETCH NEXT FROM my_Cursor select @NEWNUM2 = MAX(num2) from TEST if @NEWNUM2 is null Begin set @NEWNUM2 = 0 end set @NEWNUM2 = @NEWNUM2 + 1 UPDATE TEST SET num2 = @NEWNUM2 WHERE num1 = @PROC_NEWNUM1 END; CLOSE my_Cursor; DEALLOCATE my_Cursor; 

Answer 2

Take a look at the inserted pseudo-table in your trigger, as it will contain several rows during these operations. You should always handle multiple lines in your triggers.

See here for more details:

How to check the actions of multiple rows in a SQL Server trigger?

Answer 3

The trigger must be rewritten to handle multiple row inserts. Never write a trigger using variables. All triggers should think that someday someone is going to do multi-line input / update / delete.

You should not increase columns this way in a trigger, if you need to increase column numbers, why aren't you using an identifier column?

Answer 4

 INSERT INTO dbo.media_queue (table_name, table_id, media_id, tunnel, sub_tunnel, event) SELECT 'media_info' ,i.id ,i.session_id ,i.tunnel ,i.sub_tunnel ,NULL FROM INSERTED i; END