how to handle multiple records in trigger?
How do I convert this trigger code so that it can handle multiple records?
Thank you.
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
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.
God bless,
Chris.
ASKER
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.