how to do a transaction


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: how to do a transaction

  1. #1
    ranga raghunathan Guest

    how to do a transaction


    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

  2. #2
    C. E. Buttles Guest

    Re: how to do a transaction

    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



  3. #3
    ranga raghunathan Guest

    Re: how to do a transaction


    "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


  4. #4
    C. E. Buttles Guest

    Re: how to do a transaction

    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
    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center