Hi Murray,
To test if only PrintStatus has been updated use columns_updated()
--see Books Online for columns_updated section in CREATE TRIGGER
ie: this assumes PrintStatis is the 4th column in the table
if (columns_updated() = 8)
return
A trigger for Insert and Update will fire ONCE for each insert or update statement. So if 100 rows were inserted
the trigger still only fires once and the inserted table contains 100 rows. That's why you're getting the error:
subquery returned more than one row. The select statement below is returning 100 different CarrierID values
but you're trying to assign them all to @CarrierID.
SET @CarrierID = (SELECT CarrierID FROM INSERTED)
You should move the called procedure logic into your trigger and process all the rows in inserted table as a set. Something like:
if (columns_updated() = <columnBit> )
return
--insert ChargeDetailsLog
INSERT INTO ChargeDetailsLog
(ChargeDetailID, ChargeHeaderID, InvoiceNum, CarrierID, TotalFee, Memo, NCAmt)
SELECT ChargeDetailID, ChargeHeaderID, InvoiceNum, CarrierID, TotalFee, Memo, NCAmt
FROM INSERTED
--NCRemoval
UPDATE cd set
cd.NCAmt = 0
from ChargeDetails cd
join inserted i on cd.ChargeDetailID = i.ChargeDetailID
where i.NCAmt > 0
EDIT:
I forgot to mention that if your update statements always includes all columns even if the values don't change then
columns_updated() won't work. You'll need to compare individual column values between inserted and ChargeDetails table.
Be One with the Optimizer
TG
Trigger
Posted - 10/15/2005 : 09:15:13
