?
Solved

how to handle multiple records in trigger?

Posted on 2009-04-27
6
Medium Priority
?
238 Views
Last Modified: 2012-06-21
How do I convert this trigger code so that it can handle multiple records?

Thank you.
ALTER TRIGGER [dbo].[trTest] ON [dbo].[Table1] 
FOR INSERT, UPDATE
AS
BEGIN
 
DECLARE @F1 int
DECLARE @F2 int
DECLARE @F3 int
DECLARE @F4 int
DECLARE @F5 int
DECLARE @F6 int
DECLARE @F7 int
DECLARE @F8 int
DECLARE @F9 int
 
DECLARE @F10 datetime 
DECLARE @F11 char(32)
DECLARE @F12 datetime 
DECLARE @F13  datetime
DECLARE @F14 datetime
DECLARE @F15 datetime
DECLARE @F16 datetime
DECLARE @F17 varchar
 
DECLARE @F18  int
DECLARE @F19  datetime
 
SET @F18 = (SELECT COUNT(ID) FROM INSERTED WHERE ID LIKE 'COID%')
IF @F18 = 0 
BEGIN
	SET @F11=(SELECT Symbol FROM INSERTED)
	SET @F13 =(SELECT DATETODAYS FROM INSERTED)
	SET @F14=(SELECT DATEYESTERDAYS FROM INSERTED)
	SET @F15 =(SELECT DT1 FROM INSERTED)
	SET @F16 =(SELECT DT2 FROM INSERTED)
 
SET @F19=0
SET @F18 = (SELECT COUNT(CDATE) FROM Table3 WHERE (Table3.Symbol=@F11) AND (CDATE <= @F14))
 
IF @F18 >=1
BEGIN
SET @F19=(SELECT MAX(CDATE) FROM Table3 WHERE (Table3.Symbol=@F11) AND CDATE <= @F14)
END
 
IF NOT EXISTS (SELECT DISTINCT(Table3.SYMBOL) FROM Table3 WHERE (Table3.Symbol=@F11) AND (CDate = @F19 ))
BEGIN
	SET @F1=0
	SET @F4=0
	SET @F7=0
END
ELSE
BEGIN
	SET @F1=(SELECT BuyCount FROM Table3 WHERE(Table3.Symbol=@F11) AND (CDate=@F19))
	SET @F4=(SELECT SellCount FROM Table3 WHERE(Table3.Symbol=@F11) AND (CDate=@F19))
	SET @F7=(@F1-@F4)
END
 
SET @F18=(SELECT count(ID)  FROM Table1 WHERE (TimeUpdated BETWEEN @F15 AND @F16) AND (SIDE=1) AND (SymbolName=@F11) AND (IsDeleted=0))
IF @F18 = 0
BEGIN
	SET @F2 = 0
END
ELSE
BEGIN
	SET @F2=(SELECT SUM(QuantityExecuted)  FROM Table1 WHERE (TimeUpdated BETWEEN @F15 AND @F16) AND (SIDE=1) AND (SymbolName=@F11) AND (IsDeleted=0))   
END
 
SET @F18=(SELECT count(ID)  FROM Table1 WHERE (TimeUpdated BETWEEN @F15 AND @F16) AND (SIDE=2) AND (SymbolName=@F11) AND (IsDeleted=0))
IF @F18 = 0
BEGIN
	SET @F5 = 0
END
ELSE
BEGIN
	SET @F5=(SELECT SUM(QuantityExecuted)  FROM Table1 WHERE (TimeUpdated BETWEEN @F15 AND @F16) AND (SIDE=2) AND (SymbolName=@F11) AND (IsDeleted=0))   
END
	SET @F8=(@F2 - @F5)   
 
SET @F3 = @F1 + @F2
SET @F6 = @F4 + @F5
SET @F9 = @F3 - @F6
 
IF NOT EXISTS (SELECT F9.Symbol FROM F9 WHERE F9.Symbol=@F11)
BEGIN
	INSERT INTO F9(Symbol,BuyCount,SellCount,Cposition) 
	VALUES(@F11, @F3,@F6,@F9)
END
ELSE
BEGIN
	  UPDATE F9
	  Set
  		BuyCount =(@F3),
		SellCount =(@F6),
		CPosition=(@F9)
	   WHERE Symbol =@F11
END
 
IF NOT EXISTS (SELECT Table3.Symbol FROM Table3 WHERE (Table3.Symbol=@F11) AND (CDate=@F13))
BEGIN
	INSERT INTO Table3(Symbol,BuyCount,SellCount,Cposition,CDate) 
	VALUES (@F11, @F3,@F6,@F9,@F13)
END
ELSE
BEGIN
 	UPDATE Table3
  	Set
		BuyCount =(@F3),
		SellCount =(@F6),
		CPosition=(@F9)
	 WHERE (Symbol =@F11) AND (CDate=@F13)
END
 
END
 
END

Open in new window

0
Comment
Question by:madhav_ghatole
  • 3
  • 2
6 Comments
 
LVL 12

Expert Comment

by:Chris M
ID: 24241188
Hi,
I do not know what you exactly mean by saying you want it to "handle multiple records".
First of all, a trigger like this will be fired by an INSERT or UPDATE DML which may affect a singe or multiple records.
In event that a single record is affected, this trigger will only handle that single record but in event that it's multiple affected, then this trigger will be fired multiple times.
If you meant that the trigger handles multiple records as part of the side processes in the trigger then you will have to code that bit the way you want.
 
Regards,
Chris.
0
 
LVL 12

Expert Comment

by:Chris M
ID: 24241210
I would wish to clarify on the post above: In event that a sinlge DML affects multiple records then but the trigger will be fired once but will handle each record.
God bless,
Chris.
0
 

Author Comment

by:madhav_ghatole
ID: 24241424
This trigger works fine if we update one row at a time, but if we update multiple rows at a time it does not work.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 1500 total points
ID: 24242121
pastorchris: > but in event that it's multiple affected, then this trigger will be fired multiple times. < 

triggers absolutely DO NOT fire multiple times if multiple records are effected.  And it will not handle multiple records unless you setup some sort of looping through the records or use all set based statements.

0
 
LVL 12

Expert Comment

by:Chris M
ID: 24248358
Hi, I corrected that imistake n the post just before yours.
Please note that if a single transaction is affecting multiple records, then the magic tables will also contain multiple records.
The best thing is to program your trigger to handle an event irrespective of how many rows a DML affects.
All the best,
Chris.
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 1500 total points
ID: 24253206
PC: You still said "will be fired once but will handle each record.".  The way it is written, it will not.  YOU have to make it handle multiple rows.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question