CODEWITHSUNDEEP
t-sqltriggers
wojciech.lep
wojciech.lep

Reputation: 33

Help needed with Update trigger t-sql

How to create trigger on Update in transact sql, to set another field in updated row? For example:

UPDATE table SET true_false = 1 WHERE ID = @ID

will run command:

UPDATE table SET date = GETDATE() WHERE ID = @ID

. Please help. I can't figure it out ;)

Upvotes: 0

Views: 889

Answers (1)

Joe Stefanelli
Joe Stefanelli

Reputation: 135799

Keep in mind that you must always allow for the possibility of multi-row updates in any trigger you write.

create trigger tr_U_YourTable
on YourTable
for Update
as
begin
    if update(true_false)
        update yt
            set date = getdate()
            from Inserted i
                inner join Deleted d
                    on i.ID = d.ID
                inner join YourTable yt
                    on i.ID = yt.ID
            where coalesce(i.true_false,0) <> coalesce(d.true_false,0)
end

Upvotes: 3

Related Questions