Gandoif989:

Thank you so much for your help. I implemented your code into Oracle. During the inserting data into table, it generated this error " ORA-06519 active autonomous transaction detected and rolled back". Here are the code for procedure and trigger. I will continue to work on it tommorow. Thanks a lot.


CREATE OR REPLACE PROCEDURE BOP_procedure
AS

PRAGMA AUTONOMOUS_TRANSACTION;
STG_ROWS NUMBER;
STG_AMOUNT NUMBER;
RENEW_DATE DATE;

BEGIN

SELECT COUNT(NDW_TRANS_NO) STG_ROWS, SUM(AMOUNT) STG_AMOUNT, SYSDATE
INTO STG_ROWS, STG_AMOUNT, RENEW_DATE
FROM NDW_BOP_DETAIL_M_TEST;

INSERT INTO BOP_DETAIL_AUDIT ( AUDIT_NUM, STG_ROWS, STG_AMOUNT, RENEW_DATE)
VALUES (BOP_SEQ.NEXTVAL, STG_ROWS, STG_AMOUNT, RENEW_DATE);

END BOP_procedure;
**********************************************************

CREATE OR REPLACE TRIGGER BOPUPDATE
AFTER INSERT OR UPDATE ON NDW_BOP_DETAIL_M_TEST
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
BOP_procedure;
COMMIT;
END BOPUPDATE;