Oracle Database Discussions

PRAGMA AUTONOMOUS_TRANSACTION Not Working.

Hi,
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 VALUES(:new.pnum, :new.pname);
COMMIT; -- allowed only in autonomous triggers
END;


Can anyone spot my oversight ? 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;