February 5, 2010 at 3:32 am
I have two tables Temp1 and Temp2.
Data is inserted into Temp1 table continuously from the application side.
I want a trigger to insert data into Temp2 table when there is insert or update into Temp1 table.
i.e CREATE TRIGGER Trigger_name
ON TEMP1
FOR INSERT,UPDATE
AS
INSERT OR UPDATE INTO Temp2 table.
The criteria for inserting data into the Temp1 table is the combination of
Product_ID,Market_ID,Channel_ID,Period_ID,Upgrades columns should be unique and the sum(Volume) for a given combination
should get inserted or updated into Quantity column.I have created the query but it is failing for batch update or batch
insert.
Ex: 1> If there is a insert into Temp1 of unique combination
ID Product_ID,Market_ID,Channel_ID,Volume,Period_ID,Upgrades
185 6406015150.00180
1458 6406015704.00180
2587 6406015950.00180
The trigger should fire to insert a unique row into Temp2 with the sum of Volume to Quantity column.
ID Product_ID,Market_ID,Channel_ID,Quantity,Period_ID,Upgrades
200 64060151804.00180
If this combination already exists then it should update only Quantity column if not then it should insert a new
row into Temp2 table.
2>Updates:The update on Temp1 column can happen only on Volume column, the updates on other column
Product_ID,Market_ID,Channel_ID,Period_ID and Upgrades are not allowed.In the above ex1 if Volume of ID 185 gets updated
to 50 then the update should be reflected in Temp2.
ID Product_ID,Market_ID,Channel_ID,Quantity,Period_ID,Upgrades
200 64060151704.00180
Note:1>The ID column in both the table is irrelevant, independent of each other.
2>Use the attached file to create the table and fill data into the table.
February 5, 2010 at 5:06 am
Can you post the queries which are failing on batch insert/update?
--Ramesh
February 5, 2010 at 6:28 am
Note:As I told The update on Temp1 column can happen only on Volume column, the updates on other column Product_ID,Market_ID,Channel_ID
,Period_ID and Upgrades are not allowed.The updates can happen on these column but the value is always updated to zero and not to any other combination.I have created a updated trigger on temp1 where if the value updated to any of the remaining column is zero then delete the row from temp1.Upgrades will always be zero.
Ex:
ID Product_ID,Market_ID,Channel_ID,Volume,Period_ID,Upgrades
185 640 60 15 150.00 18 0
1458 640 60 15 704.00 18 0
I can update the other columns except Volumn to zero value like
update Channel_ID = 0 where id = 185
update Market_ID = 0 where id = 1458.
Once updated since this combination does not exists in Temp2 and one of column Product_ID,Market_ID,Channel_ID and Period_ID has been updated to zero this should not get inserted into Temp2.My update trigger on Temp1
deletes the row but the values of actual should get reduced by the sum(volume) of the row deleted from Temp1.
The trigger is attached.
February 5, 2010 at 7:20 am
As per the conditions mentioned, the following solution should work.
ALTER TRIGGER [dbo].[Trg_Insert_Temp2]
ON [dbo].[Temp1]
FOR INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON
DECLARE @ERROR INT
BEGIN TRAN
/*If there is insert in Temp1 and the row already exists in Temp2
then update the total sum of Quantity to Temp2 table at unit_cost level.(Only if Update is not on Quantity column)*/
IF ( EXISTS( SELECT * FROM inserted ) AND EXISTS( SELECT * FROM deleted ) )
BEGIN
INSERTTemp2( Product_ID, Market_ID, Channel_ID, Period_ID, Upgrades, Quantity )
SELECTi.Product_ID, i.Market_ID, i.Channel_ID, i.Period_ID, i.Upgrades, SUM( i.Volume ) AS Volume
FROMinserted i
INNER JOIN deleted d ON i.ID = d.ID
WHERENOT EXISTS
(
SELECT*
FROMTemp2 T2
WHERET2.Product_ID = d.Product_ID AND T2.Market_ID = d.Market_ID
AND T2.Channel_ID = d.Channel_ID AND T2.Period_ID = d.Period_ID AND T2.Upgrades = d.Upgrades
)
GROUP BY i.Product_ID, i.Market_ID, i.Channel_ID, i.Period_ID, i.Upgrades
UPDATET2
SETT2.Quantity = ( CASE WHEN i.IsChanged = 1 THEN T2.Quantity - i.Volume ELSE i.Volume END )
FROMTemp2 T2
INNER JOIN
(
SELECTd.Product_ID, d.Market_ID, d.Channel_ID, d.Period_ID, d.Upgrades, SUM( i.Volume ) AS Volume,
MAX( CASE WHEN d.Product_ID != i.Product_ID OR i.Market_ID != d.Market_ID OR i.Channel_ID != d.Channel_ID OR i.Period_ID != d.Period_ID OR i.Upgrades != d.Upgrades THEN 1 ELSE 0 END ) AS IsChanged
FROMdeleted d
INNER JOIN inserted i on d.ID = i.ID
GROUP BY d.Product_ID, d.Market_ID, d.Channel_ID, d.Period_ID, d.Upgrades
) i ON T2.Product_ID = i.Product_ID AND T2.Market_ID = i.Market_ID
AND T2.Channel_ID = i.Channel_ID AND T2.Period_ID = i.Period_ID AND T2.Upgrades = i.Upgrades
END
ELSE
IF ( EXISTS( SELECT * FROM inserted ) )
BEGIN
INSERTTemp2( Product_ID, Market_ID, Channel_ID, Period_ID, Upgrades, Quantity )
SELECTi.Product_ID, i.Market_ID, i.Channel_ID, i.Period_ID, i.Upgrades, SUM( i.Volume ) AS Volume
FROMinserted i
WHERENOT EXISTS
(
SELECT*
FROMTemp2 T2
WHERET2.Product_ID = i.Product_ID AND T2.Market_ID = i.Market_ID
AND T2.Channel_ID = i.Channel_ID AND T2.Period_ID = i.Period_ID AND T2.Upgrades = i.Upgrades
)
GROUP BY i.Product_ID, i.Market_ID, i.Channel_ID, i.Period_ID, i.Upgrades
UPDATET2
SETT2.Quantity = i.Volume
FROMTemp2 T2
INNER JOIN
(
SELECTProduct_ID, Market_ID, Channel_ID, Period_ID, Upgrades, SUM( Volume ) AS Volume
FROMinserted
GROUP BY Product_ID, Market_ID, Channel_ID, Period_ID, Upgrades
) i ON T2.Product_ID = i.Product_ID AND T2.Market_ID = i.Market_ID
AND T2.Channel_ID = i.Channel_ID AND T2.Period_ID = i.Period_ID AND T2.Upgrades = i.Upgrades
END
COMMIT TRAN
END
--Ramesh
February 5, 2010 at 7:39 am
Sorry, the trigger is not giving the desired results I did a check
Select sum(volume) from temp1 where upgrades = 0 and period_id = 36 and product_id = 585 and market_id = 58
The result is 406.00
Select * from temp2 where upgrades = 0 and period_id = 36 and product_id = 585 and market_id = 58
The value in quantity column is 1.00, it should have been 406.00
February 5, 2010 at 8:23 am
Can you give a more elaborate example with input/output data?
--Ramesh
February 5, 2010 at 9:12 am
Yes, It works like this Today I am going to import data through application to Temp1 table, in sql terms Insert data into
Temp1 table.
INSERT INTO [Temp1]
([ID],[Product_ID],[Market_ID],[Channel_ID],[Volume],[Period_ID],[Upgrades])
VALUES (481,656,171,51,100,36,0)
As soon the insert happens on Temp1 the Trigger should fire to insert data into Temp2 table.
so the value in temp2 is
[Product_ID],[Market_ID],[Channel_ID],[Actual],[Period_ID],[Upgrades]
65617151100360
Now there is another insert into Temp1 table
INSERT INTO [Temp1]
([ID],[Product_ID],[Market_ID],[Channel_ID],[Volume],[Period_ID],[Upgrades])
VALUES (1085,656,171,51,150,36,0)
Since the combination of [Product_ID],[Market_ID],[Channel_ID],[Period_ID],[Upgrades] is already present in Temp2 table
the trigger should not insert new rows into Temp2 table, It should sum the Volume column for this combination and
update or replace the Actual with new value.If this combination does not exists in Temp2 table then it should insert a new row in Temp2.
so the value in temp2 is
[Product_ID],[Market_ID],[Channel_ID],[Actual],[Period_ID],[Upgrades]
65617151250360
Now if I update the Volume column in Temp1 table for ID 1085 to 100, then the trigger should look in temp2 table for
this updated combination and update or replace the Actual with new sum value.
so the value in temp2 is
[Product_ID],[Market_ID],[Channel_ID],[Actual],[Period_ID],[Upgrades]
65617151200360
February 6, 2010 at 12:50 am
Upto this I've understood correctly. But you mentioned that one can also update other columns such as Product_ID, Market_ID etc., What would be the impact of these updates on Temp2 table?
--Ramesh
February 6, 2010 at 1:39 am
yes we can update other column such as Product_id,channel_id but only to zero value Take this ex:
ID Product_ID,Market_ID,Channel_ID,Volume,Period_ID,Upgrades
185 640 60 15 150.00 18 0
1458 640 60 15 704.00 18 0
2587 640 60 15 950.00 18 0
The trigger should fire to insert a unique row into Temp2 with the sum of Volume to Quantity column.
ID Product_ID,Market_ID,Channel_ID,Quantity,Period_ID,Upgrades
200 640 60 15 1804.00 18 0
Here if I update Market_id of ID 185 it will be only to 0 not to 70,100,....
or Update channel_id of ID 2587 to zero and not to any value of 16,14,...
Once the value gets update to zero I use the trigger but not mentioned in the create trigger to reduce the Quantity value in Temp2 from Temp1 which got updated to 0 and delete the row from the Temp1.
Update temp1 set Market_id = 0 where id = 185
The trigger should fire to update the Quantity value in Temp2 to reduce the
by value which got updated to zero.
1804.00 - 150.00 = 1654.00
ID Product_ID,Market_ID,Channel_ID,Quantity,Period_ID,Upgrades
200 640 60 15 1654.00 18 0
February 6, 2010 at 1:59 am
After few modifications, I came to this solution and I hope it should work fine this time.
/****** Object: Trigger [dbo].[Trg_Insert_Temp2] Script Date: 02/06/2010 13:09:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Trg_Insert_Temp2]
ON [dbo].[TEMP1]
FOR INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON
DECLARE @ERROR INT
BEGIN TRAN
/*If there is insert in Temp1 and the row already exists in Temp2
then update the total sum of Quantity to Temp2 table at unit_cost level.(Only if Update is not on Quantity column)*/
IF ( EXISTS( SELECT * FROM inserted ) AND EXISTS( SELECT * FROM deleted ) )
BEGIN
UPDATET2
SETT2.Quantity = ( CASE WHEN i.IsChanged = 1 THEN T2.Quantity - i.OldVolume ELSE T2.Quantity + ( i.NewVolume - i.OldVolume ) END )
FROMTemp2 T2
INNER JOIN
(
SELECTd.Product_ID, d.Market_ID, d.Channel_ID, d.Period_ID, d.Upgrades,
SUM( d.Volume ) AS OldVolume, SUM( i.Volume ) AS NewVolume,
MAX( CASE WHEN d.Product_ID != i.Product_ID OR i.Market_ID != d.Market_ID OR i.Channel_ID != d.Channel_ID OR i.Period_ID != d.Period_ID OR i.Upgrades != d.Upgrades THEN 1 ELSE 0 END ) AS IsChanged
FROMdeleted d
INNER JOIN inserted i on d.ID = i.ID
GROUP BY d.Product_ID, d.Market_ID, d.Channel_ID, d.Period_ID, d.Upgrades
) i ON T2.Product_ID = i.Product_ID AND T2.Market_ID = i.Market_ID
AND T2.Channel_ID = i.Channel_ID AND T2.Period_ID = i.Period_ID AND T2.Upgrades = i.Upgrades
INSERTTemp2( Product_ID, Market_ID, Channel_ID, Period_ID, Upgrades, Quantity )
SELECTi.Product_ID, i.Market_ID, i.Channel_ID, i.Period_ID, i.Upgrades, SUM( i.Volume ) AS Volume
FROMinserted i
INNER JOIN deleted d ON i.ID = d.ID
WHERENOT EXISTS
(
SELECT*
FROMTemp2 T2
WHERET2.Product_ID = d.Product_ID AND T2.Market_ID = d.Market_ID
AND T2.Channel_ID = d.Channel_ID AND T2.Period_ID = d.Period_ID AND T2.Upgrades = d.Upgrades
)
GROUP BY i.Product_ID, i.Market_ID, i.Channel_ID, i.Period_ID, i.Upgrades
END
ELSE
IF ( EXISTS( SELECT * FROM inserted ) )
BEGIN
UPDATET2
SETT2.Quantity = T2.Quantity + i.Volume
FROMTemp2 T2
INNER JOIN
(
SELECTProduct_ID, Market_ID, Channel_ID, Period_ID, Upgrades, SUM( Volume ) AS Volume
FROMinserted
GROUP BY Product_ID, Market_ID, Channel_ID, Period_ID, Upgrades
) i ON T2.Product_ID = i.Product_ID AND T2.Market_ID = i.Market_ID
AND T2.Channel_ID = i.Channel_ID AND T2.Period_ID = i.Period_ID AND T2.Upgrades = i.Upgrades
INSERTTemp2( Product_ID, Market_ID, Channel_ID, Period_ID, Upgrades, Quantity )
SELECTi.Product_ID, i.Market_ID, i.Channel_ID, i.Period_ID, i.Upgrades, SUM( i.Volume ) AS Volume
FROMinserted i
WHERENOT EXISTS
(
SELECT*
FROMTemp2 T2
WHERET2.Product_ID = i.Product_ID AND T2.Market_ID = i.Market_ID
AND T2.Channel_ID = i.Channel_ID AND T2.Period_ID = i.Period_ID AND T2.Upgrades = i.Upgrades
)
GROUP BY i.Product_ID, i.Market_ID, i.Channel_ID, i.Period_ID, i.Upgrades
END
COMMIT TRAN
END
--Ramesh
February 6, 2010 at 2:43 am
Thanks Ramesh its working according to my requirement.
February 6, 2010 at 2:45 am
I am glad I could help.
--Ramesh
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply