i have a problemdeling with triggers

North_Wind · January 14th, 2005, 08:40 PM

problem discription:
i made a trigger (insted of update),an it works correctly, but if the updte statement matches more than one record an error appeared
ex: if i have products table,and i want to update the unitprice for the products which have Supplier id of 4 (ofcourse, many product my be supplied by one supplier).
all what i need from my trigger is: if the unit new price is less than the old one , the trigger puts the new in the audi table and rollback the transaction.
ANY ONE CAN HELP?????

jemacc · January 16th, 2005, 03:50 AM

Post your trigger

here is an example

Code:

CREATE TRIGGER yourTriggerNameHere
ON yourTable
AFTER INSERT
AS
DELETE FROM yourTable
FROM yourTable
INNER JOIN inserted ON inserted.col = yourTable.col AND inserted.[col2] = yourTable.[col2]
WHERE inserted.col3 = 'value' AND yourTable.col3 <> 'value'

Notes:

1.A ROLLBACK in a trigger following a DELETE would also roll back the DELETE. It would (as intended) also rollback the initial insert that caused the trigger.
2.If this is SQL 2000, you could consider using an INSTEAD OF trigger, which, instead of having to delete the row(s) just inserted, could avoid inserting it to begin with.

Jaime E. Maccou