SQL Server Performance

Why is this trigger still working?

Discussion in 'SQL Server 2005 General Developer Questions' started by DilliGrg, Aug 7, 2007.

  1. DilliGrg Member

    I am not clear about the RETURN within the trigger (
    IF UPDATE (user_modified) OR UPDATE (date_modified)
    RETURN) of this part. How is this trigger still updating those columns after RETURN statement? Thanks
    DilliGrgCREATE TRIGGER dbo.ut_audit_TableA ON dbo.TableA AFTER
    UPDATE AS BEGIN
    IF UPDATE (user_modified) OR UPDATE (date_modified)
    RETURNUPDATE dbo.TableA
    SET date_modified = GETDATE(), user_modified = SUSER_SNAME()FROM dbo.TableA a INNER JOIN Inserted b ON a.id = b.id
    IF (@@ERROR <> 0)
    BEGIN
    IF (@@TRANCOUNT > 0)
    ROLLBACK TRANEND
    END
  2. FrankKalis Moderator

    Not sure what exactly you mean, but the trigger jumps into the IF condition only when one of these two columns is affected by an INSERT od UPDATE. If not, it just goes ahead and updates the tows in the UPDATE statement.
  3. Adriaan New Member

    RETURN simply stops the trigger code, in no way does it affect the implicit transaction.
    To rollback the implicit transaction, you need to execute ROLLBACK TRANS (big surprise). And if there is any trigger code left after that, which you do not need to run through if you've done the rollback, then add RETURN after the ROLLBACK TRANS to stop the trigger code.
  4. DilliGrg Member

    Ok probably I wasn't clear explaining the issue here. This is what I was thinking: when there is a RETURN condition after IF UPDATE (user_modified) OR UPDATE (date_modified), as soon as the condition satisfies, it should stop there and do not perform the update statement after this line. But in reality, it is updating even after the RETURN condition. So my question is does this RETURN doing anything in this trigger and why is it there or does it have to be there to check that condition? Looks to me that it is doing as IF UPDATE (user_modified) OR UPDATE (date_modified) RETURN UPDATE...
    Thanks for the responses Frank and Adriaan.
  5. Adriaan New Member

    Activate a trace to monitor your update, see what code is showing up on the trace.

Share This Page