Getting Started with Adobe After Effects - Part 6: Motion Blur
A collection of quick technology learning tips from what people around you learn every day

SQL Server - Trigger should always work on multiple rows

Apr 26 2012 12:00AM by vanne040   

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.

Read More..   [32134 clicks]

Published under: SQL Server Tips ·  ·  ·  · 


vanne040
84 · 2% · 657
5
 
6
 
 
0
Incorrect
 
0
Interesting
 
0
Forgotten



Submit

1  Comments  

  • Thank you. Well done sampling.

    commented on Apr 26 2012 2:51PM
    rwillemain
    669 · 0% · 50

Your Comment


Sign Up or Login to post a comment.

"SQL Server - Trigger should always work on multiple rows" rated 5 out of 5 by 5 readers
SQL Server - Trigger should always work on multiple rows , 5.0 out of 5 based on 5 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]