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:

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?

A cursor inside a trigger is a horribly bad idea; cursors are notoriously slow and memory hogs, and anything in a trigger ought to be as lean and fast as ever humanly possible. I would strongly advise to use a different approach (from my own, personal and agonizing experience with such constructs)
–
marc_sApr 24 '13 at 18:09

Though the above is not entirely correct and has some errors, it did help me by giving me a foundation to work with. I have added the updated version of the above in my question.
–
pugFeb 2 '10 at 11:45

1

I'm sorry, but a cursor is not the best way to do this.
–
ErikEFeb 7 '10 at 7:56

@Cocowalla You do a JOIN between the INSERTED table and the base data table. To get incrementing values for the update, use Row_Number() (SQL 2005 and up) or an insert to a temp table with an identity column, or a Numbers table with a million rows or so (adds only 13 megabytes to the database). In fact, better than all this is to replace the column with an identity column that gives incrementing values already!
–
ErikEOct 31 '11 at 18:08

Trigger needs to be rewriteen to handle multiple row inserts. Never write a trigger like that using variables. All triggers must alawys consider that someday someone is going to do a multi-row insert/update/delete.

You shouldn't be incrementing columns that way in a trigger either, if you need incremented column numbers why aren't you using an identity column?

I didn't like the way the columns were being incremented either and I have even changed the trigger in Oracle to use a sequence. As for identity, my first thought was to use alter table to change it to an identity but unfortunately that is not possible and other ways to do it require more changes than I am brave enough to hazard.
–
pugFeb 2 '10 at 5:41