CODEWITHSUNDEEP
sqlt-sqltriggerssql-server-2012insert
TryNCode
TryNCode

Reputation: 441

Insert if any column has changed except if only one particular one has

In TSQL, is it possible to detect if the values in the columns have changed? I would like to insert a row from a trigger if any column has changed but if a particular column (created_at) is the only thing that's changed, then do not insert anything.

I have this but it still inserts every time, even it nothing has changed

BEGIN
INSERT INTO dbo.Changes (
            [name] 
            --... other columns
            [created_at]
       )
SELECT 
            ins.[name],
            ins.[created_at]                
FROM INSERTED ins 
WHERE
      [name] != ins.[name] OR 
      [age] != ins.[age]

How can I only insert if something has changed from any column except created_at?

Thanks

Upvotes: 0

Views: 459

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Assuming your table has a primary key, you can do:

SELECT ins.[name], ins.[created_at]                
FROM INSERTED ins JOIN
     DELETED del
     ON ins.id = del.id
WHERE del.[name] <> ins.[name] OR 
      del.[age] <> ins.[age];

Note that your logic does not handle NULL values correctly.

You can also do similar logic without DELETED by using UPDATE(). However, this is a strange function, because it operates on the entire set in inserted and not on a row-by-row basis. Hence, a column will be called "updated" if it is in the SET clause -- even if the value does not change.

Upvotes: 1

Related Questions