MSSQL Database Table Trigger not inserting records into another table after INSERT/UPDATE event

Question

I am working on a MSSQL Database (for a program called Sage 200). There are many tables in the database, however, I want to be notified of changes (new record inserted or existing record updated) via Trigger on a specific table.

I also want to support multiple rows on this table being updated at same time also.

When a record is inserted or updated, I want to take a specific field from the table and insert/update another table with that field's value.

So, to put it into perspective; the trigger looks like this:

CREATE TRIGGER [dbo].[IC_CustomerLocationChanges] ON [dbo].[SLCustomerLocation]
AFTER INSERT,UPDATE
AS
BEGIN

    SELECT RowNum = ROW_NUMBER() OVER(ORDER BY SLCustomerAccountID) , SLCustomerAccountID
    INTO #CustomerLocationChanges
    FROM INSERTED;

    DECLARE @MaxRownum INT;
    SET @MaxRownum = (SELECT MAX(RowNum) FROM #CustomerLocationChanges);

    DECLARE @Iter INT;
    SET @Iter = (SELECT MIN(RowNum) FROM #CustomerLocationChanges);

    WHILE @Iter <= @MaxRownum
    BEGIN

        -- Get Customer account Id
        DECLARE @SLCustomerAccountID INT = (SELECT SLCustomerAccountID FROM #CustomerLocationChanges WHERE RowNum = @Iter);

        -- Check If Customer Doesn't Already Exist In Queue Table
        IF ((SELECT COUNT(*) FROM IC_CustomerUpdates WITH (NOLOCK) WHERE SLCustomerAccountID = @SLCustomerAccountID) > 0)
        BEGIN

            -- Insert new record
            print 'Insert [CustomerCreate] Queue Entry | SLCustomerAccountID : ' + CAST(@SLCustomerAccountID AS VARCHAR(255));
            INSERT INTO IC_CustomerUpdates (SLCustomerAccountID, Synced) VALUES
            (@SLCustomerAccountID, 0);

        END
        ELSE
        BEGIN

            -- Update existing record
            print 'Update [CustomerCreate] Queue Entry | SLCustomerAccountID : ' + CAST(@SLCustomerAccountID AS VARCHAR(255));
            UPDATE IC_CustomerUpdates SET Synced = 0 
            WHERE SLCustomerAccountID = @SLCustomerAccountID;

        END

        SET @Iter = @Iter + 1;

    END

    DROP TABLE #CustomerLocationChanges;

END
GO

To test this, I ran the following query:

update SLCustomerLocation SET AddressLine2 = AddressLine2 + ' test' 
where SLCustomerAccountID = 1019

select * from IC_CustomerUpdates

This returns no rows from my IC_CustomerUpdates:

This is what I see in the message window:

This means the trigger hasn't inserted a record into my queue table. Any ideas what might be wrong?

Doesn't this condition: IF ((SELECT COUNT(*) FROM IC_CustomerUpdates WITH (NOLOCK) WHERE SLCustomerAccountID = @SLCustomerAccountID) > 0) means that you'll only insert rows if there already exist a row for that costumer in that table? — Lamak
You could (and probably should) rewrite this as a set based operation instead of RBAR. And there really is no reason to put the contents of inserted into a temp table. You already have a copy, no need to create yet another. And be careful with those NOLOCK hints....they are not just dirty reads. blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere — Sean Lange
@Lamak - yes you are right. That condition is wrong. It should be: IF ((SELECT COUNT(*) FROM IC_CustomerUpdates WITH (NOLOCK) WHERE SLCustomerAccountID = @SLCustomerAccountID) = 0) - just tested it; it's working fine now. Sorry about that, silly typo caused entire trigger not to work... — Latheesan
@SeanLange I plan to submit this to code review to ask for advice. I need the exact same trigger on several other tables also, so not sure how to achieve that without repeating the same trigger code for each table. — Latheesan
Ok, so now it works. But there are a lot of things that should be improved. There are details such as SET @Iter = (SELECT MIN(RowNum) FROM #CustomerLocationChanges);, shouldn't the first time that you are setting this variable be just SET @Iter = 1?. Anyway, the whole thing can be rewritten to be set based instead — Lamak

Adam Byrne Adam Byrne · Accepted Answer · 2016-08-02T08:39:12

@Lamak looks to be correct; stumbled on this a bit late but maybe this info will help others.

Sicon have developed a 3rd party add-on that helps audit changes. This might cover what you need.

Most integrations to Sage 200 use the OpLock field in each table to determine the last edit date.

SLCustomerLocation table can hold multiple records for a single SLCustomerAccountID so you might need to take this into consideration with your script.

MSSQL Database Table Trigger not inserting records into another table after INSERT/UPDATE event

1 Answers