asked on

SQL Update trigger comparing old and new value

Hello experts,

I need a trigger that rolls back an update of record, if that record update changes the value of a field to another value.

ALTER TRIGGER [dbo].[TimezoneRollback] 
   ON  [dbo].[UserSettings] 
   FOR UPDATE
AS 
DECLARE @i int
SET @i = 0
			Select @i = count(u.userid)
			FROM UserSettings AS u JOIN inserted AS i ON i.UserId = u.UserId 
			WHERE i.TimeZoneCode != u.TimeZoneCode
IF(@i > 0)
BEGIN	
			ROLLBACK TRANSACTION;
			RETURN
END

Open in new window

So I wrote a "FOR UPDATE" Trigger, which checks the current value of the timezone and compares that to the value of the timezone in the INSERTED table.
When those values don't match (for me this means that the update included a different timezone than the current one), a rollback happens.

Unfortunately this doesn't work. The variable @i always is 0, no matter if I execute an update with a different timezone.

Can anyone help me?
Thanks!

25112

in the query, change the "Select @i = count(u.userid)" part to select *
to see what exactly it brings back.. will show you quickly if it's a data issue you don't realize yet..

then comment out
"WHERE i.TimeZoneCode != u.TimeZoneCode"
and try select * again...

ASKER CERTIFIED SOLUTION

dwkor

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

arthrex

ASKER

That was the perfect hint. You need to JOIN the INSERTED Table with the DELETED Table. In those two tables you have the old value and the new value