Hi. This SHOULD be something simple, but I am apparently missing something. I have a Users table where a user's status is a varchar(100). I'm trying to implement a trigger so that when a user's status is changed to any string other than what it was before the update the trigger would change the LastUpdated field to current date/time.
Here's the trigger, I replaced the update of LastUpdated with a simple print statement. For some reason, it seems like the trigger is firing after the update statement has committed because the values of @m_status_new and @m_status_old are always the same so this trigger always prints 'status has not changed.' What am I doing wrong? Thank you greatly for any help provided.
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'tr_update_users' AND type = 'TR')
DROP TRIGGER tr_update_users
CREATE TRIGGER tr_update_users
DECLARE @m_status_new varchar(100),
SELECT @m_status_new = UserStatus,
@m_UserID = UserID
SELECT @m_status_old = UserStatus
WHERE UserID = @m_UserID
IF @m_status_new != @m_status_old
print 'status has changed'
print 'status has not changed'
Originally posted by PlatinumRiver
So what I should be doing is read everything from the inserted table into a cursor, loop over the cursor and check the deleted table for every inserted record to see if the status has changed.
Thanks for the help!!!
You could but do not have to use cursor, try something like this:
update u set updated=getdate()
from users u join inserted i on i.id=u.id
join delete d on d.id=u.id