0

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

1
1

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;
1
  • Thanks! I was trying to use a regular trigger instead, but was getting a different error and found a solution to that is to use the compound trigger. May 27 at 8:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.