how to do a transaction

[ranga raghunathan]

I have a stored procedure that inserts a record into table1. There is a INSERT
trigger on table1 that uses the just-inserted-record-in-table1 and updates
a record in table2. I want both the stored proecedure and the trigger to
be in a transaction .. any ideas/suggestions would be appreciated.

thanks
ranga

[C. E. Buttles]

Revise your stored procedure to do both. You don't say why you want to do
this, but it is doable.

"ranga raghunathan" <ranga1@msn.com> wrote in message
news:38d1915c$1@news.devx.com...
>
> I have a stored procedure that inserts a record into table1. There is a
INSERT
> trigger on table1 that uses the just-inserted-record-in-table1 and updates
> a record in table2. I want both the stored proecedure and the trigger to
> be in a transaction .. any ideas/suggestions would be appreciated.
>
> thanks
> ranga

[ranga raghunathan]

"C. E. Buttles" <cebuttle@sprintsvc.net> wrote:
>Revise your stored procedure to do both. You don't say why you want to
do
>this, but it is doable.
>

well, i don't want to do both in the same stored procedure.. while i understand
that that is one option, i want to find out if sql server can create and
hold the transaction from a stored proc to a trigger and roll their work
if either fails.

on a similar note, can i start a transaction in one stored procedure, and
then before committing, call another stored proc and expect to carry the
transaction to the second stored proc as well ?


thanks for your reply
ranga

[C. E. Buttles]

No and no. Triggers fire at the time of an insert, update or delete. Any
roll back would have to be part of the trigger.

You can't begin a transaction in one sproc and do the commit as part of a
second, either, unless you call the second within the first sproc with the
intention of receiving a result to continue processing the first sproc or of
performing some calculation, etc., that will have a bearing on the results
of the first sproc. Rollbacks and commits would have to occur within their
respective sprocs, however, since once you leave one, it must either commit
or rollback.

If you use a single sproc with nested BEGIN TRANS... COMMITS you can roll
everything back from the point the roll back starts to the inner most pair.


"ranga raghunathan" <ranga1@msn.com> wrote in message
news:38d3e138$1@news.devx.com...
>
> "C. E. Buttles" <cebuttle@sprintsvc.net> wrote:
> >Revise your stored procedure to do both. You don't say why you want to
> do
> >this, but it is doable.
> >
>
> well, i don't want to do both in the same stored procedure.. while i
understand
> that that is one option, i want to find out if sql server can create and
> hold the transaction from a stored proc to a trigger and roll their work
> if either fails.
>
> on a similar note, can i start a transaction in one stored procedure, and
> then before committing, call another stored proc and expect to carry the
> transaction to the second stored proc as well ?
>
>
> thanks for your reply
> ranga
>