Triggers and Locks

I have a question about how an AFTER action on a trigger affects locks. If process A updates a row in a table with an "AFTER" trigger, will process A's lock be released before the "AFTER" action takes place, or will process A's lock be held until the "AFTER" action is completed. I am trying to solve a deadlock problem that seems to be related to triggers/stored procedures, and would very much appreciate any response. Thanks, Tim Owens BellSouth Applied Technologies

Tim Owens wrote: > > I have a question about how an AFTER action on a trigger affects locks. > > If process A updates a row in a table with an "AFTER" trigger, will > process A's lock be released before the "AFTER" action takes place, > or will process A's lock be held until the "AFTER" action is completed. > > I am trying to solve a deadlock problem that seems to be related to > triggers/stored > procedures, and would very much appreciate any response. The lock has to be held until the end of the transaction which certainly includes the AFTER action of the UPDATE trigger. If you are executing a singleton UPDATE statement, without a BEGIN WORK the scope of the transaction is actually the entire UPDATE statement and therefore the locks will not be released until the entire UPDATE completes on all affected rows. If you have started an explicit transaction, ie BEGIN WORK; ...... COMMIT WORK; then that is the scope of the transaction. Art S. Kagel