Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Posts
    8

    Angry Unanswered: UPDATE trigger firing too late?

    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.

    USE myDB
    IF EXISTS (SELECT name FROM sysobjects
    WHERE name = 'tr_update_users' AND type = 'TR')
    DROP TRIGGER tr_update_users
    GO

    CREATE TRIGGER tr_update_users
    on dbo.Users
    FOR UPDATE
    AS

    DECLARE @m_status_new varchar(100),
    @m_status_old varchar(100),
    @m_UserID int

    SELECT @m_status_new = UserStatus,
    @m_UserID = UserID
    FROM inserted

    SELECT @m_status_old = UserStatus
    FROM Users
    WHERE UserID = @m_UserID


    IF @m_status_new != @m_status_old
    BEGIN
    print 'status has changed'
    END
    ELSE
    BEGIN
    print 'status has not changed'
    END
    GO

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    -- here you got new status !!

    SELECT @m_status_new = UserStatus,
    @m_UserID = UserID
    FROM inserted

    -- here you got also new status !! Use deleted for getting old status

    SELECT @m_status_old = UserStatus
    FROM Users
    WHERE UserID = @m_UserID
    ------------ use this one
    SELECT @m_status_old = UserStatus
    FROM deleted
    WHERE UserID = @m_UserID

    and remember that it is possible to have updated more than one record at once...

  3. #3
    Join Date
    Oct 2003
    Posts
    8
    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!!!


    Originally posted by snail
    -- here you got new status !!

    SELECT @m_status_new = UserStatus,
    @m_UserID = UserID
    FROM inserted

    -- here you got also new status !! Use deleted for getting old status

    SELECT @m_status_old = UserStatus
    FROM Users
    WHERE UserID = @m_UserID
    ------------ use this one
    SELECT @m_status_old = UserStatus
    FROM deleted
    WHERE UserID = @m_UserID

    and remember that it is possible to have updated more than one record at once...

  4. #4
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    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
    where d.status<>i.status

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Avoid cursors...especially in triggers...

    Very little that can't be accomplished with out them....

    Think set based....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    where d.status<>i.status
    ...will fail logically if either status value is null.

    Use one of these alternatives to check for content changes:
    1) where isnull(d.status, '')<>isnull(i.status, '')
    2) where Checksum(d.status) <> Checksum(i.status)

    If you need to detect case-sensitive changes as well, try this:
    3) where Binary_Checksum(d.status) <> Binary_Checksum(i.status)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Thumbs up

    Originally posted by blindman
    where d.status<>i.status
    ...will fail logically if either status value is null.

    Use one of these alternatives to check for content changes:
    1) where isnull(d.status, '')<>isnull(i.status, '')
    2) where Checksum(d.status) <> Checksum(i.status)

    If you need to detect case-sensitive changes as well, try this:
    3) where Binary_Checksum(d.status) <> Binary_Checksum(i.status)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •