• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1763
  • Last Modified:

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!
0
arthrex
Asked:
arthrex
1 Solution
 
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
 
dwkorCommented:
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
 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now