Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2004
    Posts
    3

    Unanswered: HELP! What can i use instead of BEGIN ATOMIC in PL/SQL

    I'm using Oracle 9i, and I wanted to do the folowing trigger in PL/SQL.

    Code:
    CREATE OR REPLACE TRIGGER trigger1 INSTEAD OF INSERT ON view1
    REFERENCING NEW AS nrow
    FOR EACH ROW
    BEGIN ATOMIC
    
      INSERT INTO table1 VALUES(...);
      INSERT INTO table2 VALUES(...);
    
    END;
    /
    I know i can't use the BEGIN ATOMIC in PL/SQL, but i wanted to know what can i use instead of it.

    I'm having problems because i want to insert values on table1 that refer to the values i'm inserting in table2 (Foreign Key), and the values in table2 refer the ones in table1 (another Foreign Key).

    How can i insert both at the same time, like with the BEGIN ATOMIC...

    Thk for your help

    R04KH
    Last edited by r04kh; 12-03-04 at 19:33.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down

    BEGIN ATOMIC?
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Presumably BEGIN ATOMIC means something in some other database. Unfortunately we don't know what it is.

    Having STFW, it appears to be a DB2 term for a facility that saves having to write exception handlers. Are you saying you want both INSERTs to succeed or fail together, but SAVEPOINTs are not allowed within a trigger?
    Last edited by WilliamR; 12-04-04 at 16:45.

  4. #4
    Join Date
    Dec 2004
    Posts
    3
    Quote Originally Posted by WilliamR
    Presumably BEGIN ATOMIC means something in some other database. Unfortunately we don't know what it is.

    Having STFW, it appears to be a DB2 term for a facility that saves having to write exception handlers. I still don't quite see what it's for though, or exactly what the problem is that you need to solve. Are you saying you want both INSERTs to succeed or fail together, but SAVEPOINTs are not allowed within a trigger?
    I'm not even sure BEGIN ATOMIC would work in other DataBase programs... but it appears to work in DB2 (i guess...)

    What i'm trying to do is to insert a row in Table2 that as a FK to the row i'm going to insert in Table1. This way i'll have to insert the row in Table1 before i insert the other in Table2.

    So far so good... the problem is that the row i'm inserting in Table2 also has a
    FK to the row i'm inserting on Table1. This way i would have to insert first on Table2.

    ... so what i really wanted to do was to insert in both tables "at the same time", maintaining the integrity of the database.

    I would like oracle to only check the integrity of the database, in the end of both inserts.


    I want both inserts to suceed because the FK constraints should only be checked in the end. Although i can't make it happen....


    Thk for your help!

    R04KH
    Last edited by r04kh; 12-04-04 at 17:02.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I guess I understand what you want ... I'd suggest you to redesign foreign key constraints in a way that check is done at the commit point (instead of insert in your case). Check the example at the top of this page (deferring constraint checking).

  6. #6
    Join Date
    Dec 2004
    Posts
    3

    Talking

    Thk that really helps ... I'll go try it right now

    R04KH

Posting Permissions

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