Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181

    Unanswered: Transaction question

    Hi,
    I had a cron set up that would insert into table A from table B and then truncate table B as follows:
    set transaction read write;
    insert into online_regs_hold_tst select * from online_regs_test;
    truncate table online_regs_test;
    commit;

    I thought that by using the set transaction I was setting up an "all or nothing" senario - I'm wrong by the looks of things. I did a test and if the the insert fails it still truncates the table.
    Is there a way to set this up so that you have - if the insert fails, the truncte fails ?

    Thanks for any help,
    Breen.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    SET TRANSACTION does not do what you think. Since you are writing a SQL Plus script, you could do this:

    WHENEVER SQLERROR EXIT
    insert into online_regs_hold_tst select * from online_regs_test;
    truncate table online_regs_test;

    (The final COMMIT is unnecessary because TRUNCATE TABLE, being DDL, causes an implicit COMMIT anyway).

  3. #3
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Thanks Andrew,
    That's exactly what I needed.
    Cheers,
    Breen.

Posting Permissions

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