Solved

SQL Update trigger comparing old and new value

Posted on 2011-09-19
3
1,264 Views
Last Modified: 2012-06-22
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
Comment
Question by:arthrex
3 Comments
 
LVL 5

Expert Comment

by:25112
Comment Utility
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
 
LVL 13

Accepted Solution

by:
dwkor earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:arthrex
Comment Utility
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now