Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2004
    Posts
    2

    Unanswered: Trouble with inserting new rows - Need Help

    Hello,

    Environment> Oracle 9i release 2.
    I created a Oracle trigger that invokes a stored procedure when a row is inserted. Both trigger and procedure resides in the same schema. After a row is inserted in the table as shown in (exhibit A), it invokes a stored procedure that will load the row information to a similiar-like table belonging to another schema (exhibit B). The trigger and stored procedure sends back a successfull message stating the row was successfully inserted. However, when I review the table that was inserted by the stored procedure (as shown in exhibit B) I cannot see the new row. The new row is present because when I tried to insert the same information again I received a duplicate error message. I feel there is an issue of a missing "commit" statement somewhere, but it is my undertanding you can't include a "commit" statement within a trigger.

    Can anyone shed some light on this?

    ------------- Exhibit: A ---------------------
    CREATE OR REPLACE TRIGGER PRISM.TRG_RESERVATION_INS_ROW
    AFTER INSERT
    ON PRISM.RESERVATION
    REFERENCING NEW AS NEW OLD AS OLD
    begin
    PRISM.SP_INS_RESERVATION;
    end;
    ------------- Exhibit: B ---------------------
    CREATE OR REPLACE PROCEDURE sp_ins_RESERVATION
    as
    begin
    EXECUTE IMMEDIATE '
    insert into PRISMR.RESERVATION
    (RESERVATION_PK ,
    ROOMSTAYRESERVATION ,
    CONFIRMATIONID ,
    RESERVATIONID ,
    RESERVATIONORIGINATORCODE ,
    ORIGINALBOOKINGDATE ,
    CANCELLATIONID ,
    CANCELORIGNATORCODE ,
    CANCELLATIONDATE ,
    STAYDATERANGE ,
    RECTYPE ,
    WEEKNUMBER ,
    PRECHECKIN ,
    ACCOMDATIONCODE ,
    ORIGINALUSERID ,
    LASTCHANGEUSERID ,
    FOLIOPRINTED ,
    PRECONFERENCE ,
    DAYOFWEEK ,
    PCSENDSITE ,
    LABELPRINTED ,
    PACKAGEID ,
    PACKAGEFLAG ,
    REMINDSENTFLAG ,
    CONFIRMSENTFLAG ,
    SENDSITEFLAG ,
    SENTSITEDATE ,
    TIMEOFDAY ,
    AMPMINDICATOR ,
    FROMSITEDATE ,
    RESV_RESCOMMENT_FK ,
    RESV_SELECTEDMEMBERSHIP_FK ,
    RESV_RESGUEST_FK ,
    RESV_SPECIALREQUEST_FK ,
    RESV_SERVICE_FK ,
    RESV_ROOMSTAY_FK ,
    RESV_PROFILE_FK ,
    RESV_HOTELREFERENCE_FK ,
    RESV_LEGACYPROFILE_FK ,
    RESV_RESVMARKETING_FK)
    select
    M.RESERVATION_PK ,
    M.ROOMSTAYRESERVATION ,
    M.CONFIRMATIONID ,
    M.RESERVATIONID ,
    M.RESERVATIONORIGINATORCODE ,
    M.ORIGINALBOOKINGDATE ,
    M.CANCELLATIONID ,
    M.CANCELORIGNATORCODE ,
    M.CANCELLATIONDATE ,
    M.STAYDATERANGE ,
    M.RECTYPE ,
    M.WEEKNUMBER ,
    M.PRECHECKIN ,
    M.ACCOMDATIONCODE ,
    M.ORIGINALUSERID ,
    M.LASTCHANGEUSERID ,
    M.FOLIOPRINTED ,
    M.PRECONFERENCE ,
    M.DAYOFWEEK ,
    M.PCSENDSITE ,
    M.LABELPRINTED ,
    M.PACKAGEID ,
    M.PACKAGEFLAG ,
    M.REMINDSENTFLAG ,
    M.CONFIRMSENTFLAG ,
    M.SENDSITEFLAG ,
    M.SENTSITEDATE ,
    M.TIMEOFDAY ,
    M.AMPMINDICATOR ,
    M.FROMSITEDATE ,
    null ,
    null ,
    null ,
    null ,
    null ,
    null ,
    null ,
    null ,
    null ,
    null
    FROM PRISM.RESERVATION M
    where
    m.RESERVATION_PK not in
    (select RESERVATION_pk from RESERVATION)';
    END;

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

    Cool

    The problem is that you cannot execute DML against the same table you have the trigger on, it will give you the 'mutating table' error.

    Your procedure does this:
    Code:
    INSERT INTO PRISMR.RESERVATION
    ....
    SELECT ...
    FROM PRISM.RESERVATION M
    What you have to do is call the procedure with the data:
    Code:
    ------------- Exhibit: A ---------------------
    CREATE OR REPLACE TRIGGER PRISM.TRG_RESERVATION_INS_ROW
    AFTER INSERT
       ON PRISM.RESERVATION 
       REFERENCING NEW AS NEW OLD AS OLD
    BEGIN
      PRISM.SP_INS_RESERVATION(:NEW);
    END;
    
    ------------- Exhibit: B ---------------------
    CREATE OR REPLACE PROCEDURE SP_INS_RESERVATION
      (NEWREC PRISM.RESERVATION%ROWTYPE)
    AS
    BEGIN
      INSERT INTO PRISMR.RESERVATION
           (RESERVATION_PK 
           ,ROOMSTAYRESERVATION 
           ,
           ... ETC ...
           ,RESV_RESVMARKETING_FK) 
      VALUES 
           (NEWREC.RESERVATION_PK 
           ,NEWREC.ROOMSTAYRESERVATION 
           ,... ETC ...
           ,NULL);
    END:

    Last edited by LKBrwn_DBA; 06-29-04 at 17:50.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Your trigger seems fine, although I don't see why you are using EXECUTE IMMEDIATE to perform the insert statement. You are correct that you cannot commit within a trigger, but then nor would you want to: the whole point is that the triggered action should only be committed if the triggering statement is committed. How are you looking for the new record? Until you commit in the original session the record will not be visible to any other session.

    BTW, I don't know why everyone puts that redundant REFERENCING clause in their triggers - it's a waste of keystrokes!

Posting Permissions

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