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!
arthrexAsked:
Who is Participating?
 
dwkorConnect With a Mentor Commented:
Something like that:

ALTER TRIGGER [dbo].[TimezoneRollback] 
   ON  [dbo].[UserSettings] 
   FOR UPDATE
AS 
begin
	if @@rowcount = 0
		return

	if update(TimeZoneCode)
		if exists
		(
			select *
			from inserted i join deleted d ON i.UserId = d.UserId 
			where i.TimeZoneCode != u.TimeZoneCode
		)
		begin
			ROLLBACK TRANSACTION;
			RETURN
		end
end

Open in new window

0
 
25112Commented:
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...
0
 
arthrexAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.