Hello,
I am trying to use trigger to log the change of tables. However, it seems I have to hard code the column name and column value. Is there any generic solution regardless of the table structure?
Table structure:
CREATE table base_table ( id number(10), nm varchar2(10) );
CREATE table log_table ( id number(10), nm varchar2(10), logTs timestamp(6));
Table specific trigger works fine:
CREATE OR REPLACE TRIGGER trigger_spfc
BEFORE UPDATE ON base_table
FOR EACH ROW
BEGIN
INSERT INTO log_table( id, nm, logTs)
VALUES( :old.id, :old.nm, systimestamp);
END;
I tried to make a more generic trigger using primary key column(eg, id) only. So that I only need hard code the primary key column, but not all columns in the table.
CREATE OR REPLACE TRIGGER trigger_generic
BEFORE UPDATE ON base_table
FOR EACH ROW
BEGIN
INSERT INTO log_table( id, nm, histTS)
SELECT id, nm, systimestamp from base_table
WHERE ID = :OLD.id;
END;
Now I have "ORA-04091, table is mutating" error.
Any suggestion is welcome, thanks!