• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 240
  • Last Modified:

how to handle multiple records in trigger?

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
madhav_ghatole
Asked:
madhav_ghatole
  • 3
  • 2
2 Solutions
 
Chris MConsulting - Technology ServicesCommented:
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
 
Chris MConsulting - Technology ServicesCommented:
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
 
madhav_ghatoleAuthor Commented:
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
BrandonGalderisiCommented:
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
 
Chris MConsulting - Technology ServicesCommented:
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
 
BrandonGalderisiCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now