Trigger Problem For Multiple record

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Trigger Problem For Multiple record

Author

Topic

cshekar
Starting Member

49 Posts

Posted - 2002-06-26 : 16:59:00

Hi can any one help me
I have have trigger it work fine if I update one record at a time
but when I am update multiple record at a time it give me problem
it gives error that I have nested select statement where it return the more than record which is true, but how do you solve this
Here is my code

CREATE TRIGGER trPrice_History_Upd_ON_Price ON price
for update, insert
AS

SET NOCOUNT ON

declare @insertedID int

If update(amount) or update(package_quantity) Begin

INSERT INTO price_history
(
intProduct_id_FK,
mnyOld_amt,
mnyNew_amt,
chvUserName_ID,
intPrice_type_id,
intPricing_Zone_ID_FK,
intOld_Package_Quantity,
intNew_Package_Quantity
)
SELECT I.product_id,D.amount, I.amount,I.UserName,2--intPrice_type_id = 2 refers to change in the amount(retail price) field in the price table
, I.Pricing_Zone_ID, D.Package_Quantity, I.Package_Quantity
FROM deleted D INNER JOIN inserted I ON D.product_id = I.product_id
WHERE d.amount <> i.amount or d.package_quantity <> i.package_quantity

if (select amount from deleted) <> (select amount from inserted) or (select package_quantity from deleted) <> (select package_quantity from inserted)
select @insertedID = @@identity
else
select @insertedID = 0

END

if update(regular_price) BEGIN

INSERT INTO price_history
(
intProduct_id_FK,
mnyOld_amt,
mnyNew_amt,
chvUserName_ID,
intPrice_type_id,
intPricing_Zone_ID_FK,
intOld_Package_Quantity,
intNew_Package_Quantity

)
SELECT I.product_id,D.regular_price, I.regular_price,I.UserName,3--intPrice_type_id = 3 refers to change in the regular_price field in the price table
, I.Pricing_Zone_ID, D.Package_Quantity, I.Package_Quantity
FROM deleted D INNER JOIN inserted I ON D.product_id = I.product_id
WHERE D.regular_price <> I.regular_price

END

IF update(pricing_type_id) BEGIN

INSERT INTO event_log
(Product_ID, Event_Date, Event_Type_ID, Event_Reference_ID, Event_Activation_Flag, Pricing_Zone_ID)
SELECT I.product_id,getdate(),1,@insertedID,PTI.sale_flag, d.pricing_zone_id
FROM deleted D
INNER JOIN inserted I ON D.product_id = I.product_id
INNER JOIN pricing_type PTD on D.pricing_type_id = PTD.oid
INNER JOIN pricing_type PTI on I.pricing_type_id = PTI.oid
WHERE PTD.sale_flag <> PTI.sale_flag

END

The Problem is this portion of code
if (select amount from deleted) <> (select amount from inserted) or (select package_quantity from deleted) <> (select package_quantity from inserted)
select @insertedID = @@identity
else
select @insertedID = 0

Thanks

chandra shekar

smccreadie
Aged Yak Warrior

505 Posts

Posted - 2002-06-26 : 20:39:43

Triggers occur once per statement, not per row. In my opinion, you should rewrite this as a stored procedure that accepts multiple rows and use a trigger to call it.

cshekar
Starting Member

49 Posts

Posted - 2002-06-27 : 11:42:19

Thank You

chandra shekar

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources