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
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.
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.
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.