title: SQL Server A trigger to work on multiple row inserts
body: <p>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 <em>A</em>. Lets say that the table is called <em>test</em> with two columns <em>num1</em> and <em>num2</em>. The trigger runs on each insert of <em>num1</em> in <em>test</em>. Following is the trigger: </p>
  
  <pre><code>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
  </code></pre>
  
  <p>This works fine in simple row based inserts, but there is another 3rd party app <em>B</em> (sigh) that sometimes does multiple inserts on this table something like this but not exactly:</p>
  
  <pre><code>INSERT INTO [db1].[dbo].[test]
             ([num1])
  
     Select db1.dbo.test.num1 from [db1].[dbo].[test]
  GO
  </code></pre>
  
  <p>This causes the trigger to behave erratically...</p>
  
  <p>Now I don't have access to the source of app <em>A</em> or <em>B</em> and only control the database and the trigger. Is there anything that can be done with the trigger so that the updates done to <em>num2</em> are correct in case of multiple inserts?</p>
  
  <p><strong>Solution:</strong></p>
  
  <p>Following is the solution based on affan's code:</p>
  
  <pre><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
  </code></pre>
  
  <p>Check here for a set based approach:
  <a href="https://stackoverflow.com/questions/2183748/sql-server-rewrite-trigger-to-avoid-cursor-based-approach">SQL Server - Rewrite trigger to avoid cursor based approach</a></p>