Oracle 12: Using compound trigger to update data in same table on each insert/update

Question

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

• mustaccio: Does this answer your question? Trigger needs dbms_alert declared

Answer 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;

• SergioLeone: 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.