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

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]

        t.employeeNumber = CASE
            WHEN fuelInfoCodes.efsCode = 'DRID' THEN
            ELSE t.employeeNumber
        t.unitNumber = CASE
            WHEN fuelInfoCodes.efsCode = 'UNIT' THEN
            ELSE t.unitNumber
        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
        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
        t.driverName = CASE
            WHEN fuelInfoCodes.efsCode = 'NAME' THEN
            ELSE t.driverName
        t.subFleet = CASE
            WHEN fuelInfoCodes.efsCode = 'SSUB' THEN
            ELSE t.subFleet
    FROM fuelTransactions t
    JOIN inserted
        ON inserted.fuelTransactionid = t.id
    JOIN fuelInfoCodes
        ON fuelInfoCodes.id = inserted.fuelInfoCodeId;

I've done a little bit of testing by adding the following statement to the trigger:


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.

  • So you're hard-coding the redundant EAV data from the codes table in the transactions table, when you could derive it at query time? Is this SCD or do you otherwise expect the code values to change over time? (I suspect the CASE expression is causing folding or collapsing but I'm trying to understand the philosophy before constructing an MVCE.) Commented Feb 17, 2017 at 16:30
  • The "code values" are moved for ease of understand for some other non-technical users, who can otherwise get around the table (e.g., my boss ;) ). I do not expect the codes themselves to change, or the value, over time.
    – mwalt
    Commented Feb 17, 2017 at 16:40
  • I would consider a view for your boss instead of duplicating the data. Commented Feb 17, 2017 at 16:43

1 Answer 1


The reason it's not working is that your join causes each row to be updated multiple times, but each update sees only the original values, not the cumulative values of previous updates. So your updates are in effect cancelling the previous updates. (Or maybe SQL only allows one row update per statement. Either way the result is the same, with different resource use.)

Here's a quick and poorly formatted repro:

declare @insertedexample table (id INT, codeid int, value varchar(10))

INSERT INTO @insertedexample VALUES

declare @targetexample table (id INT, codeid1value varchar(10), codeid2value varchar(10))

INSERT INTO @targetexample VALUES
(1, 'origvalue1', 'origvalue2')

SELECT * FROM @targetexample

t.codeid1value = CASE WHEN i.codeid=1 THEN i.value ELSE t.codeid1value END,
t.codeid2value = CASE WHEN i.codeid=2 THEN i.value ELSE t.codeid2value END
FROM @targetexample t
JOIN @insertedexample i on i.id = t.id

SELECT * FROM @targetexample

The only way I can think of at the moment to handle this is by building a dynamic query.

Aaron's suggestion of a view would certainly be better, provided users will only be filtering by fuelTransactions.id or the data will remain small enough to handle filtering after pivoting. (Edit: I later realised that it might also be possible to create a view, or a set of views, or certainly a function, that would allow efficient additional filtering, dependent of course on supporting indexing in the base table.)


Actually you probably don't need a dynamic query, since the columns in the fuelTransactions table are fixed as are the efsCode codes you're interested in.

You might be able to use a pivot of one form or another, which incorporates the current values for the columns not being updated, even if you have to use a bunch of ugly subqueries.
I might have time to experiment with it, but can't promise.

  • No worries, I appreciate the insight. As the info table only receives inserts en masse once a day, at at most around 1,500 records at a time... I've simply used a cursor.
    – mwalt
    Commented Feb 17, 2017 at 18:47
  • @mwalt Haha, I almost mentioned a cursor in my first update. Considering the usage, I would have done the same.
    – T.H.
    Commented Feb 18, 2017 at 12:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.