Link to home
Start Free TrialLog in
Avatar of madhav_ghatole
madhav_ghatoleFlag for India

asked on

How to create trigger for multiple rows in update SQL Server 2005

I want to create such trigger on ORDERS table that after updating 10 or more rows
at a time it should handle all the update of rows and set the result accordingly.

In updating rows if 'side=1' then calculate the difference between new quantityExecuted updated and the old quantityExecuted and add that difference to CPosition in CURRENTPOSITION table; also add the difference in BuyCount in CURRENTPOSITION table

In updating rows if 'side=2' then calculate the difference between new quantityExecuted updated and the old quantityExecuted and subtract that difference to CPosition in CURRENTPOSITION table; also add the difference in SellCount in CURRENTPOSITION table.

I write the trigger as below:

and I tested this trigger with the query

UPDATE    ORDERS
SET              QuantityExecuted = QuantityExecuted + 1
WHERE     (SymbolName = 'FDAX200906') AND (Side = 1)

after executing this query the trigger only adds 1 in BuyCount and increaments CPosition by 1 only
But in ORDERS table 14 rows are updated so the result should be Cposition = Cposition + 14 and BuyCount = BuyCount +14

In ORDERS table OrderID is the primary key and in CURRENTPOSITION table SymbolName is the primary key.

I am not getting where I made mistake in the trigger.
How do I solve this problem?

Thanks.

 
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trOrders]
   ON  [dbo].[ORDERS]
   AFTER INSERT,UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
    -- Insert statements for trigger here
		--=====================================================
		--	CURRENTPOSITION
		--=====================================================
		--Buy Action
				UPDATE CURRENTPOSITION
				   SET	BuyCount = BuyCount + (I.QuantityExecuted - D.QuantityExecuted),
						CPosition = CPosition + (I.QuantityExecuted - D.QuantityExecuted)
				  FROM CURRENTPOSITION
				  JOIN Inserted I
					ON CURRENTPOSITION.Symbol = I.SymbolName
					AND I.SIDE = 1 
				  JOIN Deleted D
					ON D.SymbolName = I.SymbolName
					AND D.OrderID = I.OrderID
		--Sell Action
				UPDATE CURRENTPOSITION
				   SET  SellCount = SellCount + (I.QuantityExecuted - D.QuantityExecuted),
						CPosition = CPosition - (I.QuantityExecuted - D.QuantityExecuted)
				  FROM CURRENTPOSITION
				  JOIN Inserted I
					ON CURRENTPOSITION.Symbol = I.SymbolName
					AND I.SIDE = 2
				  JOIN Deleted D
					ON D.SymbolName = I.SymbolName
					AND D.OrderID = I.OrderID
END

Open in new window

Avatar of momi_sabag
momi_sabag
Flag of United States of America image

i don't exactly understand what you want, but you can do
select count(*) from inserted
in order to know how many records were inserted / updated
what about this?



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trOrders]
   ON  [dbo].[ORDERS]
   AFTER INSERT,UPDATE
AS 
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
    -- Insert statements for trigger here
                --=====================================================
                --      CURRENTPOSITION
                --=====================================================
                --Buy Action
                                UPDATE CURRENTPOSITION
                                   SET  BuyCount = BuyCount + count(I.QuantityExecuted),
                                                CPosition = CPosition + count(I.QuantityExecuted)
                                  FROM CURRENTPOSITION
                                  JOIN Inserted I
                                        ON CURRENTPOSITION.Symbol = I.SymbolName
                                        AND I.SIDE = 1 
                                  JOIN Deleted D
                                        ON D.SymbolName = I.SymbolName
                                        AND D.OrderID = I.OrderID
                --Sell Action
                                UPDATE CURRENTPOSITION
                                   SET  SellCount = SellCount + count(I.QuantityExecuted),
                                                CPosition = CPosition - count(I.QuantityExecuted)
                                  FROM CURRENTPOSITION
                                  JOIN Inserted I
                                        ON CURRENTPOSITION.Symbol = I.SymbolName
                                        AND I.SIDE = 2
                                  JOIN Deleted D
                                        ON D.SymbolName = I.SymbolName
                                        AND D.OrderID = I.OrderID
END

Open in new window

Avatar of madhav_ghatole

ASKER

I need to calculate the new quantityExecuted i .e. if old quantityExecuted=10
and we update it to 15 while side=1 then in CPosition olnly the 5 sohuld be added.

If there are number of rows which are updated then in CURRENTPOSITION correct result should be added.
well, in that case change logic for count only, don't change your logic for cposition.



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trOrders]
   ON  [dbo].[ORDERS]
   AFTER INSERT,UPDATE
AS 
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
    -- Insert statements for trigger here
                --=====================================================
                --      CURRENTPOSITION
                --=====================================================
                --Buy Action
                                UPDATE CURRENTPOSITION
                                   SET  BuyCount = BuyCount + count(I.QuantityExecuted),
                                                CPosition = CPosition + (I.QuantityExecuted - D.QuantityExecuted)
                                  FROM CURRENTPOSITION
                                  JOIN Inserted I
                                        ON CURRENTPOSITION.Symbol = I.SymbolName
                                        AND I.SIDE = 1 
                                  JOIN Deleted D
                                        ON D.SymbolName = I.SymbolName
                                        AND D.OrderID = I.OrderID
                --Sell Action
                                UPDATE CURRENTPOSITION
                                   SET  SellCount = SellCount + count(I.QuantityExecuted),
                                                CPosition = CPosition - (I.QuantityExecuted - D.QuantityExecuted)
                                  FROM CURRENTPOSITION
                                  JOIN Inserted I
                                        ON CURRENTPOSITION.Symbol = I.SymbolName
                                        AND I.SIDE = 2
                                  JOIN Deleted D
                                        ON D.SymbolName = I.SymbolName
                                        AND D.OrderID = I.OrderID
END

Open in new window

This gives an error as below
"An aggregate may not appear in the set list of an UPDATE statement."
sorry, I don't have access for SQL server for a while, have a look at .


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trOrders]
   ON  [dbo].[ORDERS]
   AFTER INSERT,UPDATE
AS 
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
    -- Insert statements for trigger here
                --=====================================================
                --      CURRENTPOSITION
                --=====================================================
                --Buy Action
                                UPDATE CURRENTPOSITION
                                   SET  BuyCount = BuyCount + (select count(II.QuantityExecuted) from Inserted II where II.SIDE=1 and II.symbolName=CURRENTPOSITION.Symbol),
                                                CPosition = CPosition + (I.QuantityExecuted - D.QuantityExecuted)
                                  FROM CURRENTPOSITION
                                  JOIN Inserted I
                                        ON CURRENTPOSITION.Symbol = I.SymbolName
                                        AND I.SIDE = 1 
                                  JOIN Deleted D
                                        ON D.SymbolName = I.SymbolName
                                        AND D.OrderID = I.OrderID
                --Sell Action
                                UPDATE CURRENTPOSITION
                                   SET  SellCount = SellCount + (select count(II.QuantityExecuted) from Inserted II where II.SIDE=1 and II.symbolName=CURRENTPOSITION.Symbol),
                                                CPosition = CPosition - (I.QuantityExecuted - D.QuantityExecuted)
                                  FROM CURRENTPOSITION
                                  JOIN Inserted I
                                        ON CURRENTPOSITION.Symbol = I.SymbolName
                                        AND I.SIDE = 2
                                  JOIN Deleted D
                                        ON D.SymbolName = I.SymbolName
                                        AND D.OrderID = I.OrderID
END

Open in new window

Above trigger only works fine but if we want to increament the QuantityExecuted by 5 then how it handles?
e.g.
UPDATE    ORDERS
SET              QuantityExecuted = QuantityExecuted + 5
WHERE     (SymbolName = 'FDAX200906') AND (Side = 1)
what about this?
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trOrders]
   ON  [dbo].[ORDERS]
   AFTER INSERT,UPDATE
AS 
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
    -- Insert statements for trigger here
                --=====================================================
                --      CURRENTPOSITION
                --=====================================================
                --Buy Action
                                UPDATE CURRENTPOSITION
                                   SET  BuyCount = BuyCount + (select count(II.QuantityExecuted) from Inserted II where II.SIDE=1 and II.symbolName=CURRENTPOSITION.Symbol),
                                        CPosition = CPosition + (I.QuantityExecuted - D.QuantityExecuted),
										QuantityExecuted = QuantityExecuted + 5
                                  FROM CURRENTPOSITION
                                  JOIN Inserted I
                                        ON CURRENTPOSITION.Symbol = I.SymbolName
                                        AND I.SIDE = 1 
                                  JOIN Deleted D
                                        ON D.SymbolName = I.SymbolName
                                        AND D.OrderID = I.OrderID
                --Sell Action
                                UPDATE CURRENTPOSITION
                                   SET  SellCount = SellCount + (select count(II.QuantityExecuted) from Inserted II where II.SIDE=1 and II.symbolName=CURRENTPOSITION.Symbol),
                                        CPosition = CPosition - (I.QuantityExecuted - D.QuantityExecuted)
										
                                  FROM CURRENTPOSITION
                                  JOIN Inserted I
                                        ON CURRENTPOSITION.Symbol = I.SymbolName
                                        AND I.SIDE = 2
                                  JOIN Deleted D
                                        ON D.SymbolName = I.SymbolName
                                        AND D.OrderID = I.OrderID
END

Open in new window

I did not mean that I have set the value QuantityExecuted = QuantityExecuted +1

If I fired the query below

UPDATE    ORDERS
SET              QuantityExecuted = QuantityExecuted + 5
WHERE     (SymbolName = 'FDAX200906') AND (Side = 1)

If 10 rows are updated by the query then in CURRENTPOSITION, CPosition should be increamented by 50.

i.e. 5 * 10 = 50

how it should be done?

in that case, use this one.



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trOrders]
   ON  [dbo].[ORDERS]
   AFTER INSERT,UPDATE
AS 
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
    -- Insert statements for trigger here
                --=====================================================
                --      CURRENTPOSITION
                --=====================================================
                --Buy Action
                                UPDATE CURRENTPOSITION
                                   SET  BuyCount = BuyCount + ((select count(II.QuantityExecuted) from Inserted II where II.SIDE=1 and II.symbolName=CURRENTPOSITION.Symbol)*I.QuantityExecuted),
                                        CPosition = CPosition + (I.QuantityExecuted - D.QuantityExecuted),
                                  FROM CURRENTPOSITION
                                  JOIN Inserted I
                                        ON CURRENTPOSITION.Symbol = I.SymbolName
                                        AND I.SIDE = 1 
                                  JOIN Deleted D
                                        ON D.SymbolName = I.SymbolName
                                        AND D.OrderID = I.OrderID
                --Sell Action
                                UPDATE CURRENTPOSITION
                                   SET  SellCount = SellCount + ((select count(II.QuantityExecuted) from Inserted II where II.SIDE=1 and II.symbolName=CURRENTPOSITION.Symbol)*I.QuantityExecuted),
                                        CPosition = CPosition - (I.QuantityExecuted - D.QuantityExecuted)
                                                                                
                                  FROM CURRENTPOSITION
                                  JOIN Inserted I
                                        ON CURRENTPOSITION.Symbol = I.SymbolName
                                        AND I.SIDE = 2
                                  JOIN Deleted D
                                        ON D.SymbolName = I.SymbolName
                                        AND D.OrderID = I.OrderID
END

Open in new window

SOLUTION
Avatar of RiteshShah
RiteshShah
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you for your responses

Now I want to write the simple trigger as below.

I Have written a trigger and its not working, I don't know why.

When 4 rows are updated by the query just like in previous post
then CPosition should be increamented by the desired sum.

e.g in order table
Qty
1
2
4
5

And Cposition =12 in CURRENTPOSITION

UPDATE    Orders
SET              Qty = Qty + 1
WHERE     (BS = 1)

After executing this query the
in Orders table
Qty will be
2
3
5
6

so the Cposition should be increamented by 2+3+5+6 = 16

i.e
CPosition = 12 + 16 =28
then

By the following trigger it only increaments the Cposition by 6
i.e.
CPosition = 12 + 6 =18

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trOrders]
   ON  [dbo].[Orders]
   AFTER INSERT,UPDATE
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
    -- Insert statements for trigger here
--Buy Action
            UPDATE CURRENTPOSITION
               SET CPosition = CPosition + (I.Qty)
              FROM CURRENTPOSITION
              JOIN Inserted I
                  ON CURRENTPOSITION.Symbol = I.Symbol
                  WHERE I.BS = 1
--Sell Action
            UPDATE CURRENTPOSITION
               SET CPosition = CPosition - (I.Qty)
              FROM CURRENTPOSITION
              JOIN Inserted I
                  ON CURRENTPOSITION.Symbol = I.Symbol
                  WHERE I.BS = 2
END


Please tell me how to solve this problem?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks you!
glad to help you, welcome.....