Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Trigger: is it possible to access the changing fields dynamically?

mbispingJul 15 2002
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;

...
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 16 2002
Added on Jul 15 2002
7 comments
720 views