madhav_ghatole
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.
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
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
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.
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
ASKER
This gives an error as below
"An aggregate may not appear in the set list of an UPDATE statement."
"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
ASKER
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)
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
ASKER
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?
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks you!
glad to help you, welcome.....
select count(*) from inserted
in order to know how many records were inserted / updated