Parent Proc_Child Proc Begin Commit Rollback

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Parent Proc_Child Proc Begin Commit Rollback

Author

Topic

shzkhan28
Starting Member

15 Posts

Posted - 2009-08-31 : 00:53:57

Hi All,

In Child Procs i am also mentioning Begin transaction/Commit
If Child Proc executes successfully and Commits
and after that there is problem and Rollback in Parent Proc
Will Child Proc updation be Commited or Rollback?

Proc Parent
Begin
Begin Try

Begin Transaction

insert xyz tables
update xyz2 tables

Exec Child

update xyz3 tables ...Error...

Commit
End Try
Begin Catch
Rollback
End Catch

End

Proc Child
Begin Try
Begin Transaction
insert tbl1 ...
update tbl2 ...
Commit Transaction
End Try
Begin Catch
Rollback
End Catch

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-08-31 : 13:20:14

The outer transaction affects the transactions in the child stored procedures. So even if you commit in a child stored procedure and there is an outer wrapper transaction in the parent, then on rollback in the parent will cause a rollback in the child.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

shzkhan28
Starting Member

15 Posts

Posted - 2009-09-01 : 22:45:39

Thanks...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-09-02 : 15:31:54

You're welcome.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources