The behavior described in these three examples becomes easy to see when you consider that multiple triggers assigned to a single event (UPDATE, INSERT, or DELETE) are _independent_ transactions. They are not calling one another, but responding to an event, so they can't see each other.

It would be like calling two VFP functions back to back from a main .prg and hoping to see the local variables from the first function in the second.

Nested triggers (triggers that fire in response to other actions by a first trigger) on the other hand seem like nested functions, but I do not believe that they share name space either. The documentation for SQL Server 7 makes a point of noting that multiple triggers do not fire in a particular order, and so should be independent. The Microsoft Press SQL Server 7 Implementation Training Kit also mentions this fact while discussing recursive triggers. Since nested triggers also do not call each other, but instead initiate events that trigger the trigger (ugh). These are not nested like a calling chain, but are independent.

This is unlike stored procedures which CAN call other stored procedures, thus exposing their name space, and local temp tables.

Note however that since Triggers are considered transactions (begun when the initiating event takes place), a ROLLBACK TRAN statement executed in ANY trigger will roll back the entire transaction, but subsequent code can still be executed. So a RETURN will not prevent a subsequent trigger from firing, but a ROLLBACK TRANSACTION statement issued in the first trigger will render any data changes undone. I want to see the results of this in a nested and multiple trigger setting, hmmm sounds like an evening project :)

At least that is how I understand it. Any changes or additions welcome. I am still learning SQL Server, and may not have it quite right.

HTH

Bill