Oracle 12c – Using Compound Triggers for Data Updates

oracleoracle-12c

I need some help fixing the code of a compound trigger.
I have a table with two columns that are used for auditing (upd_by and upd_time) and I'm trying to create a trigger that would update these columns on each insert/update to the table.

The table is:

CREATE TABLE customer_info(
    id NUMBER NOT NULL,
    name VARCHAR(50) NOT NULL,
    last_upd_by VARCHAR(25) NOT NULL,
    last_upd_time TIMESTAMP NOT NULL,
    PRIMARY KEY(id)
);

I found some examples on using compound triggers, and this is the code I have so far:

CREATE OR REPLACE TRIGGER customer_info_trg
    FOR UPDATE OR INSERT ON custommer_info
    COMPOUND TRIGGER
    TYPE r_customer_info_type IS RECORD (
        id   custommer_info.id%TYPE
    );

    TYPE t_customer_info_type IS TABLE OF r_customer_info_type
        INDEX BY PLS_INTEGER;

    t_customer_info   t_customer_info_type;

    AFTER EACH ROW IS BEGIN
        t_customer_info (t_customer_info.COUNT + 1).id := :NEW.id;
    END AFTER EACH ROW;

    AFTER EACH STATEMENT IS BEGIN
        FOR indx IN 1 .. t_customer_info.COUNT
        LOOP
            UPDATE customer_info
            SET last_upd_by = USER, last_upd_time = SYSTIMESTAMP
            WHERE id = t_customer_info (indx).id;
        END LOOP;
    END AFTER EACH STATEMENT;
END;

But when I run update on the table I get ORA-04098: trigger 'customer_info_trg'k is invalid and failed re-validation

Best Answer

There is no need for a compound trigger. Use simply

CREATE OR REPLACE TRIGGER customer_info_trg
    BEFORE UPDATE OR INSERT ON custommer_info
    FOR EACH ROW    
BEGIN
    :NEW.last_upd_by := USER;
    :NEW.last_upd_time := SYSTIMESTAMP;
END;
Related Question