Trigger: is it possible to access the changing fields dynamically?
Hello,
I want to create a trigger which records all changes on a table to a protocol-table.
For example, there is this table
CHANGING_TABLE
==============
field_1 VARCHAR2(30),
field_2 VARCHAR2(30)
When updating FIELD_1 or FIELD_2 the old and new-values should be written to the table
PROTOCOL
==============
field_name VARCHAR2(50),
old_value VARCHAR2(512),
new_value VARCHAR2(512)
I can solve this by creating an update-trigger on Table CHANGING_TABLE:
...
IF (UPDATING('field_1') THEN
INSERT INTO protocol VALUES ('field_1',:old.field_1,:new.field_1);
END IF;
IF (UPDATING('field_2') THEN
INSERT INTO protocol VALUES ('field_2',:old.field_2,:new.field_2);
END IF;
...
If my table contains many columns, the trigger will become very large.
Now my question:
Is it possible to write a trigger which dynamically reads the table structure
(USER_TAB_COLUMNS) to receive and recognize the columns that have changed?
Like this:
...
FOR
rec_columns
IN
(SELECT column_name FROM USER_TAB_COLUMNS WHERE table_name = 'CHANGING_TABLE')
LOOP
IF (UPDATING(rec_columns.column_name)) THEN
INSERT INTO protocol VALUES (
rec_columns.column_name,
:old.???, /* how can I access the :old and :new -Rowtypes? */
:new.???);
END IF;
END LOOP;
...