I have a trigger that updates multiple columns in a table for rows inserted into another table.
A record inserted into the fuelTransactionInfo column, updates a column in fuelTransactions, based on the "info code", finding the correct row using fuelTransactionId. I'm just denormalizing some data for easier reporting.
The rows can be inserted into the fuelTransactionInfo column with multiple values. E.g.,
INSERT INTO fuelTransactionInfo (...cols) VALUES (...values), (...values), (...values);
My issue is that only the first set of values is being used in the trigger. I understand that triggers are fired on the statement, not the rows, and I believe the trigger code accounts for that. However, it's simply not working.
Here is my trigger code:
ALTER TRIGGER [dbo].[fuelTransactionInfoDev_DenormalizeFuelTransactions] ON [ERPDev].[dbo].[fuelTransactionInfo] AFTER INSERT AS BEGIN SET NOCOUNT ON UPDATE t SET t.employeeNumber = CASE WHEN fuelInfoCodes.efsCode = 'DRID' THEN inserted.value ELSE t.employeeNumber END, t.unitNumber = CASE WHEN fuelInfoCodes.efsCode = 'UNIT' THEN inserted.value ELSE t.unitNumber END, t.contractId = CASE WHEN fuelInfoCodes.efsCode = 'CNTN' THEN (SELECT c.id FROM pinContract p JOIN contracts c ON c.contractNumber = p.ContractNumber WHERE pin = inserted.value) ELSE t.contractId END, t.contract = CASE WHEN fuelInfoCodes.efsCode = 'CNTN' THEN (SELECT c.contractNumber FROM pinContract p JOIN contracts c ON c.contractNumber = p.ContractNumber WHERE pin = inserted.value) ELSE t.contract END, t.driverName = CASE WHEN fuelInfoCodes.efsCode = 'NAME' THEN inserted.value ELSE t.driverName END, t.subFleet = CASE WHEN fuelInfoCodes.efsCode = 'SSUB' THEN inserted.value ELSE t.subFleet END FROM fuelTransactions t JOIN inserted ON inserted.fuelTransactionid = t.id JOIN fuelInfoCodes ON fuelInfoCodes.id = inserted.fuelInfoCodeId; END
I've done a little bit of testing by adding the following statement to the trigger:
SELECT * FROM INSERTED INTO insertedTest
I see all the expected rows in the insertedTest table. I've tested the UPDATE statement by converting it into an equivalent select (based on the insertedTest table, instead of "inserted"), and all the joins are good.
Only the column in the 'UNIT' case is actually updated in fuelTransactions, which happens to be the first row in every single insert firing the trigger.
What am I doing wrong? Let me know if you need more info.