[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2009-04-29
15
Medium Priority
?
466 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:madhav_ghatole
  • 8
  • 6
15 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 24267466
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
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24267493
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

0
 

Author Comment

by:madhav_ghatole
ID: 24267567
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.
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 31

Expert Comment

by:RiteshShah
ID: 24267578
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

0
 

Author Comment

by:madhav_ghatole
ID: 24267786
This gives an error as below
"An aggregate may not appear in the set list of an UPDATE statement."
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24267810
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

0
 

Author Comment

by:madhav_ghatole
ID: 24277547
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)
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24277563
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

0
 

Author Comment

by:madhav_ghatole
ID: 24277787
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?

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24277824
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

0
 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 1500 total points
ID: 24277949
above code may show you error as I forgot to remove one "," before FROM CURRENTPOSITION.
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

0
 

Author Comment

by:madhav_ghatole
ID: 24278034
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?
0
 
LVL 31

Accepted Solution

by:
RiteshShah earned 1500 total points
ID: 24278085
try 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
--Buy Action
            UPDATE CURRENTPOSITION
               SET CPosition = CPosition + (select sum(II.qty) from Inserted II where II.SIDE=1 and II.symbolName=CURRENTPOSITION.Symbol)
              FROM CURRENTPOSITION
              JOIN Inserted I
                  ON CURRENTPOSITION.Symbol = I.Symbol 
                  WHERE I.BS = 1
--Sell Action
            UPDATE CURRENTPOSITION
               SET CPosition = CPosition - (select sum(II.qty) from Inserted II where II.SIDE=2 and II.symbolName=CURRENTPOSITION.Symbol)
              FROM CURRENTPOSITION
              JOIN Inserted I
                  ON CURRENTPOSITION.Symbol = I.Symbol
                  WHERE I.BS = 2
END

Open in new window

0
 

Author Comment

by:madhav_ghatole
ID: 24285258
Thanks you!
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24285260
glad to help you, welcome.....
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Loops Section Overview
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?

872 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