SQL & PL/SQL

Hi,

Oracle 11g r2.

I know the topic title is not very clear let me give more details.

I have to audit some table to log all modifications (inserts, updates, deletes).

I wrote the following trigger (works as expected) :

-- Trigger for DOCUMENT table audit
CREATE OR REPLACE TRIGGER TAUDIT_IUD_DOCUMENT
AFTER INSERT OR UPDATE OR DELETE ON DOCUMENT
FOR EACH ROW
DECLARE
    l_user VARCHAR2(64);
    l_date DATE;
    l_row CLOB;
    l_separator varchar2(3) := ' | ';
    l_action varchar2(16);
BEGIN
    -- get user
    l_user := nvl(v('APP_USER'), USER);
    -- get date
    SELECT sysdate INTO l_date FROM dual;
    -- if inserting, record new values
    IF INSERTING THEN
        l_action := 'INSERT';
        l_row :=     'DOCUMENT_ID=' || :NEW.DOCUMENT_ID || l_separator ||
                'ARCHIVE_ID=' || :NEW.ARCHIVE_ID || l_separator ||
                'SERVICE_ID=' || :NEW.SERVICE_ID || l_separator ||
                'DOCUMENT_TYPE_ID=' || :NEW.DOCUMENT_TYPE_ID || l_separator ||
                'STORAGE_MEDIUM_ID=' || :NEW.STORAGE_MEDIUM_ID || l_separator ||
                'DOCUMENT_STATUS_ID=' || :NEW.DOCUMENT_STATUS_ID || l_separator ||
                'LOCATION_ID=' || :NEW.LOCATION_ID || l_separator ||
                'REQUEST_ID=' || :NEW.REQUEST_ID || l_separator ||
                'DOC_NAME=' || :NEW.DOC_NAME || l_separator ||
                'URL=' || :NEW.URL || l_separator ||
                'IS_CONFIDENTIAL=' || :NEW.IS_CONFIDENTIAL || l_separator ||
                'DOC_CODE=' || :NEW.DOC_CODE || l_separator ||
                'DOC_VER=' || :NEW.DOC_VER || l_separator ||
                'DESTRUCTION_DT=' || :NEW.DESTRUCTION_DT || l_separator ||
                'CREATION_DT=' || :NEW.CREATION_DT || l_separator ||
                'COMMENTS=' || :NEW.COMMENTS || l_separator ||
                'AUTHOR=' || :NEW.AUTHOR || l_separator || 
                'ROOM_NAME=' || :NEW.ROOM_NAME;
    -- if updating then record only changed values
    ELSIF UPDATING THEN
        l_action := 'UPDATE';
        [...]
    -- if deleting, record old values
    ELSIF DELETING THEN
        l_action := 'DELETE';
        [...]
    ELSE
                NULL;
    END IF;
    -- insert values
    IF l_row IS NOT NULL THEN
        INSERT INTO AUDIT_TABLE (DA_ID, DA_USER, DA_DATE, DA_ACTION, DA_TABLE, DA_ROW) 
        VALUES (S_AUDIT_TABLE.NEXTVAL, l_user, l_date, l_action, 'DOCUMENT', l_row);
    END ID;
END;

Is there any way I can improve this ?

I mean getting columns name and value (:NEW and :OLD) dynamically, instead of specifying them one by one (the trigger will have to be updated in case a new column is added).

Something like this :

FOR i in 1..DOCUMENT.COLUMN_COUNT LOOP
    l_row := l_row || DOCUMENT.COLUMN_NAME(i) || :NEW.COLUMN_VALUE(i) || l_separator;
END LOOP;

Any idea ?

Thanks.

Yann.