CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Internet Engineer
Internet Engineer

Reputation: 2534

After Update Trigger will not update - SQL Server 2008

This question is for SQL Server 2008:

I am not able to set SyncRequired = 1, when I update any of the fields below.

I am sure it's something very simple that I am missing, but I cannot figure it out.

CREATE TRIGGER [core].[updCustomersSync] ON [core].[Customers]
    AFTER UPDATE
AS
    IF ( UPDATE(Addr1)
         OR UPDATE(Addr2)
         OR UPDATE(Addr3)
         OR UPDATE(Zip)
         OR UPDATE(Contact1)
         OR UPDATE(Phone1)
         OR UPDATE(email)
         OR UPDATE(CustomerName)
       ) 
        BEGIN
            UPDATE  c
            SET     c.SyncRequired = 1
            FROM    core.Customers c
                    JOIN inserted ins ON ins.ID = c.ID
            WHERE   c.[CustomerName] <> ins.[CustomerName]
                    OR c.[Addr1] <> ins.[Addr1]
                    OR c.[Addr2] <> ins.[Addr2]
                    OR c.[Addr3] <> ins.[Addr3]
                    OR c.[Zip] <> ins.[Zip]
                    OR c.[Contact1] <> ins.[Contact1]
                    OR c.[Phone1] <> ins.[Phone1]
                    OR c.[email] <> ins.[email]
        END

Upvotes: 0

Views: 35

Answers (2)

Dave Mason
Dave Mason

Reputation: 4936

As I suggested in the comments (and as suggested by @RobertSheahan), you should use the INSERTED and DELETED tables.

CREATE TRIGGER [core].[updCustomersSync] ON [core].[Customers]
    AFTER UPDATE
AS
    /*  You can omit the IF statement.  Just let the UPDATE statement below do all the work. */
    --IF ( UPDATE(Addr1)
    --     OR UPDATE(Addr2)
    --     OR UPDATE(Addr3)
    --     OR UPDATE(Zip)
    --     OR UPDATE(Contact1)
    --     OR UPDATE(Phone1)
    --     OR UPDATE(email)
    --     OR UPDATE(CustomerName)
    --   ) 
BEGIN
    UPDATE core.Customers
    SET SyncRequired = 1
    WHERE ID IN
    (
        SELECT i.ID
        --I will assume for this example that ID is unique.
        FROM INSERTED I
        JOIN DELETED d 
            ON d.ID = i.ID

        --Why all the NULL comparisons?  Well, presumably, if someone updates an existing value to NULL,
        --or an existing value is NULL and a new value is provided, you want to know.  Right?
        WHERE   d.[CustomerName] <> i.[CustomerName] OR (d.[CustomerName] IS NULL AND i.[CustomerName] IS NOT NULL) OR (i.[CustomerName] IS NULL AND d.[CustomerName] IS NOT NULL)
        OR d.[Addr1] <> i.[Addr1] OR (d.[Addr1] IS NULL AND i.[Addr1] IS NOT NULL) OR (i.[Addr1] IS NULL AND d.[Addr1] IS NOT NULL)
        OR d.[Addr2] <> i.[Addr2] OR (d.[Addr2] IS NULL AND i.[Addr2] IS NOT NULL) OR (i.[Addr2] IS NULL AND d.[Addr2] IS NOT NULL)
        OR d.[Addr3] <> i.[Addr3] OR (d.[Addr3] IS NULL AND i.[Addr3] IS NOT NULL) OR (i.[Addr3] IS NULL AND d.[Addr3] IS NOT NULL)
        OR d.[Zip] <> i.[Zip] OR (d.[Zip] IS NULL AND i.[Zip] IS NOT NULL) OR (i.[Zip] IS NULL AND d.[Zip] IS NOT NULL)
        OR d.[Contact1] <> i.[Contact1] OR (d.[Contact1] IS NULL AND i.[Contact1] IS NOT NULL) OR (i.[Contact1] IS NULL AND d.[Contact1] IS NOT NULL)
        OR d.[Phone1] <> i.[Phone1] OR (d.[Phone1] IS NULL AND i.[Phone1] IS NOT NULL) OR (i.[Phone1] IS NULL AND d.[Phone1] IS NOT NULL)
        OR d.[email] <> i.[email] OR (d.[email] IS NULL AND i.[email] IS NOT NULL) OR (i.[email] IS NULL AND d.[email] IS NOT NULL)
    )
END

Upvotes: 1

Robert Sheahan
Robert Sheahan

Reputation: 2100

You're creating an AFTER update so by the time it runs Customers should aleady be updated. Change your logic to use deleted instead of core.customers and it should work

Upvotes: 2

Related Questions