I ended my last tip about triggers on the note 'Triggers should always be written to handle multiple rows'. I searched online for different methods people have been using on handling trigger based solutions and thought it would make a good learning tip for friends here.

There is some nice code in the links below. Please check it out and do post any other tips that are useful in regards to handling triggers.

Cursor based approach

Sample 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

Set based approach

Sample Code

SELECT @MAXNUM2 = MAX(num2) FROM TEST
if @MAXNUM2 IS NULL
BEGIN
    SET @MAXNUM2=0
END

UPDATE TEST
SET num2 = @MAXNUM2 + SubQuery.R
FROM
(
SELECT num1, ROW_NUMBER() OVER (ORDER BY num1) as R FROM inserted
)
SubQuery
INNER JOIN TEST on SubQuery.num1 =  TEST.num1

Obviously, its always best to go for the set based approach as much possible.

But there might be situations where cursors are the only way to go.

If you have few more minutes do read this nice post

http://www.brentozar.com/archive/2009/01/triggers-need-to-handle-multiple-records/

I could get to a solution to my problem from these links. Hope they are useful to others here.

Published under: SQL Server Tips ·  ·  ·  ·