How to get the main ID of updated row and use it before DML in trigger

Question

I'm blocked with the development of an Oracle trigger (11g). The goal of the trigger is to store some data in an audit trail table. One of the fields that is populated in this table is an XML block generated by a function in a package. This function has 1 argument: The ID of the row to be updated / deleted.

So I first wrote this trigger:

CREATE OR REPLACE TRIGGER TRG_TEMPLATE_FORMAT
BEFORE UPDATE OR DELETE
ON TEMPLATE_FORMAT
FOR EACH ROW

DECLARE
    v_TEMPLATE_XML          CLOB;  
    v_TEMPLATE_NAME         VARCHAR2(128);
    v_TEMPLATE_FULL_NAME    VARCHAR2(128);
BEGIN
    IF UPDATING THEN
        SELECT TEMPLATE_NAME INTO v_TEMPLATE_NAME FROM TEMPLATES WHERE TEMPLATE_ID =: OLD.TEMPLATE_ID;
        SELECT TEMPLATE_FULL_NAME INTO v_TEMPLATE_FULL_NAME FROM TEMPLATES WHERE TEMPLATE_ID = :OLD.TEMPLATE_ID;
        v_TEMPLATE_XML := PKG_TEMPLATE_MANAGEMENT.GET_TEMPLATE_XML(:OLD.TEMPLATE_ID);   
        INSERT INTO TEMPLATES_BACK(
            TEMPLATE_ID,
            TEMPLATE_FULL_NAME,
            TEMPLATE_NAME,
            EVENT_TYPE,
            EVENT_TIMESTAMP,
            OLD_XML_TEMPLATE_SOURCE        
        )
        VALUES(
            :OLD.TEMPLATE_ID,
            v_TEMPLATE_FULL_NAME,
            v_TEMPLATE_NAME,
            'UPDATE ON TEMPLATE_FORMAT',
            SYSDATE,
            v_TEMPLATE_XML
        );
    ELSIF DELETING THEN
        SELECT TEMPLATE_NAME INTO v_TEMPLATE_NAME FROM TEMPLATES WHERE TEMPLATE_ID = :OLD.TEMPLATE_ID;
        SELECT TEMPLATE_FULL_NAME INTO v_TEMPLATE_FULL_NAME FROM TEMPLATES WHERE TEMPLATE_ID = :OLD.TEMPLATE_ID;
        v_TEMPLATE_XML := PKG_TEMPLATE_MANAGEMENT.GET_TEMPLATE_XML(:OLD.TEMPLATE_ID);
        INSERT INTO TEMPLATES_BACK(
            TEMPLATE_ID,
            TEMPLATE_FULL_NAME,
            TEMPLATE_NAME,
            EVENT_TYPE,
            EVENT_TIMESTAMP,
            OLD_XML_TEMPLATE_SOURCE        
        )
        VALUES(
            :OLD.TEMPLATE_ID,
            v_TEMPLATE_FULL_NAME,
            v_TEMPLATE_NAME,
            'DELETE FROM TEMPLATE_FORMAT',
            SYSDATE,
            v_TEMPLATE_XML
        );
    END IF;
END;
/

As the TEMPLATE_FORMAT table is used in the GET_TEMPLATE_XML() Function, of course, I get the old "good" "Table is mutating" error...

I thought about using a compound trigger after searching a bit, and call my function in the BEFORE STATEMENT block. Problem is that the :OLD / :NEW binds, can't be used in that block

Final Goal of this XML is to capture the values of several records, in this XML before any modification, so it can be easily rolled back.

Is there another way I could have missed to handle this case ?

Thanks in advance

Answer 1

Solved by adding pragma autonomous_transaction in called function (PKG_TEMPLATE_MANAGEMENT.GET_TEMPLATE_XML).

Thanks again @ekochergin