Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    5

    Unanswered: Multiple Inserts with in one Stored Procedure

    If I need to update four tables within one stored procedure, how can I capture each inserts return (Success/Failure code) Code?

    I am assuming I might have to wrap it with a Transaction (either in the SP or in my VB.NET Codebehind ?!)

    Senerio1,
    - Insert into Table1
    - Insert into Table2
    - Insert into Table3
    - Insert into Table4
    - If Return code per all fout inserts are Successful, then Commit, else Rollback and Fling and Exception at the User.

    What would need to be done if Table1 generated (Table Trigger) a Value for Table2's insert ?!

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    begin
    Insert into Table1
    exception when ...
    message to user
    return;
    end;

    begin
    Insert into Table2
    exception when ...
    message to user
    return;
    end;

    begin
    Insert into Table3
    exception when ...
    message to user
    return;
    end;

    begin
    Insert into Table4
    exception when ...
    message to user
    return;
    end;

    you can use Oracle's set of exceptions (ie, dup_val_on_index, etc ...)
    or create your own.

    HTH
    Gregg

  3. #3
    Join Date
    Feb 2004
    Posts
    45
    Do you want to capture the return code for a reason, or do you simply want to abandon the transaction if any of the inserts fail ?
    Give some thought to why it would matter which insert failed as much as it matters that one of them did.
    It was working just 5 minutes ago - I promise !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •