SQL Server A trigger to work on multiple row inserts
sql-server, t-sql
Solution
You just have to open a cursor on INSERTED and iterate it for @PROC_NEWNUM1 and put your rest of code that loop. e.g
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;
Problem
I am maintaining some code that has a trigger on a table to increment a column. That column is then used by a 3rd party application A. Lets say that the table is called test with two columns num1 and num2. The trigger runs on each insert of num1 in test. 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 3rd party app B (sigh) that sometimes does multiple inserts on this table something like this but not exactly: ``` INSERT INTO [db1].[dbo].[test] ([num1]) Select db1.dbo.test.num1 from [db1].[dbo].[test] GO ``` This causes the trigger to behave erratically... Now I don't have access to the source of app A or B and only control the database and the trigger. Is there anything that can be done with the trigger so that the updates done to num2 are correct in case of multiple inserts? Solution: Following is the solution based on affan's code: ``` 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 here for a set based approach: SQL Server - Rewrite trigger to avoid cursor based approach