Trouble with inserting rows - Need Help

[john9]

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;

 

[carp]

You are right - you DO need a commit. But the commit must come from the transaction that inserted the original row. When the trigger fires, anything the trigger does is part of the same transaction. Consequently, until the owning transaction commits, you cannot see the row in the second table from a different session. Likewise, if the owning transaction is rolled back, the row in the second table will also be removed.

 

[SantaMufasa]

John,

Correct, you cannot have a COMMIT statement inside a trigger, but the trigger insert uses the external commit that you issue following the "manual" or application insert. So, however it is that you are effecting the original insert, it is from that venue that you should issue an explicit COMMIT.

Please make that change and let us know how it worked for you.

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:53 (29Jun04) UTC (aka "GMT" and "Zulu"), 14:53 (29Jun04) Mountain Time)

 

[SantaMufasa]

It will be so nice when Doug Murphy (Tek-Tips site programmer) implements a feature that notifies posters that another poster has done something during our composing a response. That way it won't always look like Carp, Sem, and I are sharing a brain. Since Carp and I have shared a brain since we worked at Oracle together in the late 1990's, our posting the same thing at nearly the same moment shouldn't be a surprise to anybody.

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:57 (29Jun04) UTC (aka "GMT" and "Zulu"), 14:57 (29Jun04) Mountain Time)

 

[sem]

Is this a row-level trigger (you're referencing new as new!)? If so, you probably get mutating error! If not, you don't need (and can not use!) new and old.
I also can not understand the need in dynamic sql and query on the same table as you may insert record from row-level trigger using explicit :new values. And after all you're inserting into PRISMR.RESERVATION records from the same PRISMR.RESERVATION table that don't exist in YOUR_SCHEMA.RESERVATION. Obviously there's no such record! Change your FROM and WHERE clauses


Regards, Dima

http://seminihin.narod.ru