[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

SQL Update trigger comparing old and new value

Posted on 2011-09-19
3
Medium Priority
?
1,694 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 5

Expert Comment

by:25112
ID: 36561696
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 2000 total points
ID: 36562768
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
ID: 36566166
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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

650 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