Greetings,
I want to create an audit table of changes to a record, but instead of keeping a copy of the whole record I want an audit table whose columns are 'primary key', column_name, Old_value, new_value, audit_dt, audit_by and insert the values (primary_id, 'column1', :Old.column1, :new.column1, sysdate, user). The problem is I want the trigger to stand up even if columns are removed or added so instead of hardcoding columnnames I want to be able to get a list of column names, compare the values of the :Old and :new for each columnname in the list, and if they're not equal insert a record into the audit table. I'm running into a problem with the way I'm trying to do it, and that is; how do you use the value of a variable as the column name in a statement. Here's the closest I came which should better show the issue, but with this one I of course am prompted to supply a value for the variable when compiling...

CREATE OR REPLACE TRIGGER "ATROBI"."INSERT_REPTEST_AUDIT" BEFORE UPDATE ON "ATROBI"."REPTEST" FOR EACH ROW
DECLARE
CURSOR reptest_fieldnames IS
SELECT column_name FROM dba_tab_columns
WHERE owner = 'ATROBI' AND table_name = 'REPTEST';
v_fieldname varchar2(30);
columnname varchar2(12) := '&v_fieldname'
BEGIN

OPEN reptest_fieldnames;
LOOP
FETCH reptest_fieldnames INTO v_fieldname;
EXIT WHEN reptest_fieldnames%NOTFOUND;

IF :new.'&columnname' <> :Old.'&columnname' THEN

INSERT INTO reptest_audit
VALUES (:new.reporter, v_fieldname, :Old.'&columnname', :new.'&columnname', SYSDATE, user);

END IF;

END LOOP;
CLOSE reptest_fieldnames;

END;

So how can you store the column name in a variable like columnname then use that variable when comparing the :new and :Old????? Thanks for any help. BTW I'm using Oracle 8i

Hi,

I do the same, but regenerate audit triggers after a structure change. Generation of audit triggers is based on tokens stored within column and table comments.

Not ideal, but works well. See attached image.

Hth
Bill

Yes, unfortunately there is simply no way to refer to :new and :old values dynamically in a trigger - the column names must be static. As Bill says, the best you can do is build a utility to re-generate the trigger code after a table change.

Can you reference columns positionally somehow so instead of saying :new.first_name you could say :new.position1? Probably not eh?

No, that would fall fould of the "no way" restriction ;-)

Here is what I'd consider doing.
CREATE TABLE CHANGE_AUDIT
( TABLE_NAME VARCHAR2(30),
COLUMN_NAME VARCHAR2(30),
OLD_VALUE VARCHAR2(4095),
NEW_VALUE VARCHAR2(2095)
)
Then the trigger just inserts the new row as data changes are made.
Yes, there is a size/datatype limitation, but it might be better than nothing.

HTH & YMMV

HAND!

It looks like you have to create a function with input parameters - table_name and output string the columns for this table (dba_tab_columns) . Then create a DDL trigger on the tables you want to audit, check after DDL if the columns where changed and if so, generate a new CREATE OR REPLACE TRIGGER DDL statement.

Once you have it, create the new trigger with the changed :NEW.colname :OLD.colname values. Looks like a bit of coding, but will be a good brain exersice..

HTH,

clio_usa - OCP 8/8i/9i DBA

Would it make sense to write a code generator that can create a file with the trigger definition?

That is probably the easiest way to do this. The advantage is that the code generator can be used for other tables, too. And can be run at regular intervals (daily, weekly, etc.) or whenever some schema changes are made.

I tried using a dynamic PL/SQL block to run from within a trigger, but Oracle complains.

Will try out a few other things and post here soon.

Ravi