CODEWITHSUNDEEP
sql-servertriggerssql-update
user2246242
user2246242

Reputation: 101

SQL Server trigger count updated columns before update

I have a table called "Users" with (id, name, lastname, dni, dob, city and lastUpdated)

What I want to do is, execute a trigger INSTEAD OF UPDATE and inside it count the number of columns that will be updated, and if the only column to be updated is "lasUpdated" column, then do not update anything.

CREATE TRIGGER USER_TRIGGER
ON USERS
INSTEAD OF UPDATE
AS
/* if the updated column is ONLY lasUpdated */
IF (COUNT(UPDATED_CLOUMNS) == 1 && UPDATED_COLUMN == "lasUpdated")
BEGIN 
ROLLBACK TRANSACTION
RETURN 
END
GO

Any idea of how can achieve it? Thank you in advance.

Upvotes: 0

Views: 223

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31785

One way is do the update in your INSTEAD OF trigger with:

WHERE inserted.Col1<>deleted.Col1
OR inserted.Col2<>deleted.Col2
etc, for all columns except "lasUpdated"

Note that if any of the columns can be NULL, you'll need to handle that in the logic as well.

Upvotes: 1

Related Questions