06-29-04, 17:22 #1Registered User
- Join Date
- Jun 2004
Unanswered: Trouble with inserting new rows - Need Help
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
REFERENCING NEW AS NEW OLD AS OLD
------------- Exhibit: B ---------------------
CREATE OR REPLACE PROCEDURE sp_ins_RESERVATION
EXECUTE IMMEDIATE '
insert into PRISMR.RESERVATION
FROM PRISM.RESERVATION M
m.RESERVATION_PK not in
(select RESERVATION_pk from RESERVATION)';
06-29-04, 17:33 #2Registered User
- Join Date
- Jun 2003
- West Palm Beach, FL
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:
INSERT INTO PRISMR.RESERVATION .... SELECT ... FROM PRISM.RESERVATION M
------------- 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
06-29-04, 18:44 #3Moderator.
Provided Answers: 1
- Join Date
- Sep 2002
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!Tony Andrews