Generic update trigger for auditing changed columns in a table.
From: Dave Akin <dakin_at_chis.com>
Date: Fri, 14 May 1999 17:17:28 -0700
Message-ID: <213%2.19$4D2.4088_at_news.uswest.net>
[Quoted] I'd like to set up a generic update trigger to check the columns in the table to see if they've been updated or not. If they have I'd like to write that particular column's value and column name out to an audittable. This is what I have so farDECLARE p_owner_out VARCHAR2(30); p_table_name_in VARCHAR2(30) := 'CERTIFICATIONS'; v_jrn_flag VARCHAR2(1) := NULL; CURSOR c_columns IS SELECT column_name, data_type FROM all_tab_columns WHERE table_name = p_table_name_in --name of table AND owner = p_owner_out; --owner of table BEGIN p_TABLE_OWNER(p_table_name_in, p_owner_out); --proc to retrieve tableowner FOR column_rec IN c_columnsLOOP IF UPDATING(column_rec.column_name) THEN
What I cannot seem to figure out is how to dynamically reference the value of the :new.xxxxx bind variable the is represented in the cursorby column_rec.column_name.Any suggestions??????? Received on Sat May 15 1999 - 02:17:28 CEST
Date: Fri, 14 May 1999 17:17:28 -0700
Message-ID: <213%2.19$4D2.4088_at_news.uswest.net>
[Quoted] I'd like to set up a generic update trigger to check the columns in the table to see if they've been updated or not. If they have I'd like to write that particular column's value and column name out to an audittable. This is what I have so farDECLARE p_owner_out VARCHAR2(30); p_table_name_in VARCHAR2(30) := 'CERTIFICATIONS'; v_jrn_flag VARCHAR2(1) := NULL; CURSOR c_columns IS SELECT column_name, data_type FROM all_tab_columns WHERE table_name = p_table_name_in --name of table AND owner = p_owner_out; --owner of table BEGIN p_TABLE_OWNER(p_table_name_in, p_owner_out); --proc to retrieve tableowner FOR column_rec IN c_columnsLOOP IF UPDATING(column_rec.column_name) THEN
INSERT INTO change_journals( operation
, schema_name
, table_name
, primary_key_value
, column_name
, textual_value) VALUES
( 'U' ,v_owner
,p_in_tbl_name
,:new.certification_id
,column_rec.column_name
,?????? to_char(:old.( value ofcolumn_name);
END LOOP;END;
What I cannot seem to figure out is how to dynamically reference the value of the :new.xxxxx bind variable the is represented in the cursorby column_rec.column_name.Any suggestions??????? Received on Sat May 15 1999 - 02:17:28 CEST