CODEWITHSUNDEEP
sqlsql-servertriggers
Ben
Ben

Reputation: 609

SQL Trigger - determine if a field has changed

I have this trigger

ALTER TRIGGER [dbo].[tInsertTaskFromOpportunityReassignment] 
   ON [dbo].[OpportunityBase]
   FOR UPDATE
AS 
BEGIN

IF UPDATE(owninguser)
BEGIN
-- do the task
END
END

I only want to do the task if owninguser has actually changed. How can I determine that?

Thanks

Upvotes: 1

Views: 4302

Answers (3)

Johan Enwall
Johan Enwall

Reputation: 1

I like to use concat although it may potentially cause you problems with NULL and '':

CONCAT('', inserted.owninguser) <> CONCAT('', deleted.owninguser)

Upvotes: 0

Dave Mason
Dave Mason

Reputation: 4936

ALTER TRIGGER [dbo].[tInsertTaskFromOpportunityReassignment] 
   ON [dbo].[OpportunityBase]
   FOR UPDATE
AS
BEGIN
/*
    I will assume that your [dbo].[OpportunityBase] table has a PRIMARY KEY
    or UNIQUE column that is immutable to join the inserted and deleted
    tables. In this example, [OpportunityBaseId] is that column.

    The SELECT query returns a set of all records that had the value of [owninguser] 
    changed. What you would do from that point is up to you.
*/
SELECT 
    i.[OpportunityBaseId], i.owninguser New_owninguser, d.owninguser Old_owninguser
FROM inserted i
JOIN deleted d
    ON i.[OpportunityBaseId] = d.[OpportunityBaseId]
    AND 
        (
            --owninguser value was changed.
            i.[owninguser] <> d.[owninguser] OR 

            --owninguser changed from non-NULL to NULL.
            (i.[owninguser] IS NULL AND d.[owninguser] IS NOT NULL) OR 

            --owninguser changed from NULL to non-NULL.
            (i.[owninguser] IS NOT NULL AND d.[owninguser] IS NULL)
        )
END 
GO

Upvotes: 0

Wes H
Wes H

Reputation: 4439

ALTER TRIGGER [dbo].[tInsertTaskFromOpportunityReassignment] 
   ON [dbo].[OpportunityBase]
   FOR UPDATE
AS 
BEGIN
DECLARE HasChanged int = 0;

SELECT @HasChanged = 1
FROM Inserted AS I
INNER JOIN Deleted AS D
ON I.PK = D.PK
AND IsNull(I.owninguser,'~') <> IsNull(D.owninguser,'~')

IF @HasChanged = 1
BEGIN
-- do the task
END
END

Compare the values of the field between Inserted and Deleted, joining the two tables on the primary key.

Upvotes: 1

Related Questions