8i PRAGMA AUTONOMOUS_TRANSACTION Not Working in Trigger ? Why

[saustin]

Good Morning,
Have the following trigger in an 8i db that does NOT save records into a log table BARCODERECEIVER_LINE
if the PRAGMA AUTONOMOUS_TRANSACTION; and COMMIT; lines are included. The save works perfectly if they are commented out. No error is displayed. It just does not save the record.
Using the example in the 8i documentation it looks like it should.

CREATE TRIGGER parts_trigger
BEFORE INSERT ON parts FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO parts_log VALUESnew.pnum, :new.pname);
COMMIT; -- allowed only in autonomous triggers
END;


Can anyone spot my oversight ? Just converted this db from 8.05 if that is of any help. Thanks, Steve.


DECLARE
mDate SYSADM.PURC_ORDER_LINE.PROMISE_DATE%type;
PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

SELECT
SYSADM.PURC_ORDER_LINE.PROMISE_DATE
INTO mDate
FROM SYSADM.PURC_ORDER_LINE
WHERE :NEWDATA.PURC_ORDER_LINE_NO = SYSADM.PURC_ORDER_LINE.LINE_NO
AND :NEWDATA.PURC_ORDER_ID = SYSADM.PURC_ORDER_LINE.PURC_ORDER_ID;
IF mDate > (SYSDATE + 30) THEN

INSERT INTO BARCODERECEIVER_LINE
(RECEIVER_ID, LINE_NO, PRINTED, QTY, PO, VENDOR, DESCRIPTION, PART, RECEIVED_DATE, PRINTER, EARLY, TRANSTIME)
SELECT
:NEWDATA.RECEIVER_ID,
:NEWDATA.LINE_NO,
'N',
:NEWDATA.RECEIVED_QTY,
:NEWDATA.PURC_ORDER_ID,
SYSADM.VENDOR.NAME,
SYSADM.PART.DESCRIPTION,
NVL(SYSADM.PURC_ORDER_LINE.PART_ID, SYSADM.PURC_ORDER_LINE.VENDOR_PART_ID),
SYSADM.RECEIVER.RECEIVED_DATE,
DECODE(SYSADM.RECEIVER.USER_ID, 'CARL', 1, 'ORLANDO', 2, 'TONYA', 3, 'MATT', 'TOM', 3, 'SYSADM', 3),
'Y',
to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS')
FROM SYSADM.RECEIVER, SYSADM.PURC_ORDER_LINE, SYSADM.PART, SYSADM.PURCHASE_ORDER, SYSADM.VENDOR
WHERE :NEWDATA.RECEIVER_ID = SYSADM.RECEIVER.ID
AND :NEWDATA.PURC_ORDER_LINE_NO = SYSADM.PURC_ORDER_LINE.LINE_NO
AND :NEWDATA.PURC_ORDER_ID = SYSADM.PURC_ORDER_LINE.PURC_ORDER_ID
AND SYSADM.PURC_ORDER_LINE.PART_ID = SYSADM.PART.ID (+)
AND SYSADM.RECEIVER.PURC_ORDER_ID = SYSADM.PURCHASE_ORDER.ID (+)
AND SYSADM.PURCHASE_ORDER.VENDOR_ID = SYSADM.VENDOR.ID (+)
AND SYSADM.RECEIVER.USER_ID IN ('ORLANDO', 'TONYA', 'MATT', 'TOM', 'SYSADM');
COMMIT;
--raise_application_error(-20000, 'Check with Purchasing please Exceeds Early Need Date');

END IF;

END;

 

[tas02]

Are you sure the process that inserts into the PARTS table does a commit? If not, it would account for this behavior.

HTH...Tom

 

[saustin]

Thanks for the reply Tom. Turns out that if you do an INSERT VALUES ... instead of INSERT SELECT there is no problem any more. This is due to autonomous transaction can't see the not yet committed data from the SELECT statement.

Here is the working code.....

DECLARE
mDate SYSADM.PURC_ORDER_LINE.PROMISE_DATE%type;
mVENDOR SYSADM.VENDOR.NAME%type;
mDESCRIPTION SYSADM.PART.DESCRIPTION%type;
mPART SYSADM.PURC_ORDER_LINE.PART_ID%type;
mVPART SYSADM.PURC_ORDER_LINE.VENDOR_PART_ID%type;

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

SELECT
SYSADM.PURC_ORDER_LINE.PROMISE_DATE
INTO mDate
FROM SYSADM.PURC_ORDER_LINE
WHERE :NEWDATA.PURC_ORDER_LINE_NO = SYSADM.PURC_ORDER_LINE.LINE_NO
AND :NEWDATA.PURC_ORDER_ID = SYSADM.PURC_ORDER_LINE.PURC_ORDER_ID;
IF mDate > (SYSDATE + 30) THEN

INSERT INTO BARCODERECEIVER_LINE (RECEIVER_ID, LINE_NO, RECEIVED_DATE, PRINTER, PRINTED, QTY, PO, EARLY, TRANSTIME)
VALUES ('', :NEWDATA.PURC_ORDER_LINE_NO, SYSDATE, '2', 'N', :NEWDATA.RECEIVED_QTY, :NEWDATA.PURC_ORDER_ID,'Y', to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS'));
COMMIT;
raise_application_error(-20000, 'Check with Purchasing please Exceeds Early Need Date');

END IF;

END;

Am now just figuring out how to add some data to the log table with a before insert trigger to complete this project.

Thanks again, Steve.

 

[saustin]

Solution !!!!!!

The SQL environment was not aware of the new transaction. It was no problem to re-write INSERT section using VALUES
instead of SELECT ...INTO

Steve