-
Hi,
I want to write a DB triger on a table which will trap the
column-name with old and new values which are updated.
The trigger should be written in a dynamic way to incorporate
any alteration (addition or deletion of columns) on the table.
Suppose the sal and deptno field of EMP table are updated.
Then the trigger will insert the column-name (sal, deptno)
along with old and new values into an another table.
So how to write this dynamic sql !! I've used dynamic sql
to achieve this, but while parsing the statement it throws
an error 'Bind Variable not declared' for the ld and :new
specifiers for the trigger.
-
Post your code, please.
Ales
-
Hi Ales,
Here is the code. Please go thru it and advice me what to do.
Thanks in advance ....
---------------------------------------------------------
create table ak_test (
id number(9),
name varchar2(50),
address varchar2(50),
pin number(10),
tel_no number(20),
primary key (id));
create table ak_aud (
id number(9),
col_name varchar2(30),
old_value varchar2(100),
new_value varchar2(100));
------------------------------------------------
create or replace trigger ak_trig
before update on ak_test
for each row
DECLARE
d_cid integer;
d_exe integer;
d_str varchar2(1000);
old_value varchar2(50);
new_value varchar2(50);
cursor c1 is select column_name col_name from user_tab_columns
where table_name = 'AK_TEST';
BEGIN
FOR r1 in c1 LOOP
d_str := 'select :old.'||r1.col_name||' , :new.'||r1.col_name||' from dual' ;
d_cid := dbms_sql.open_cursor;
dbms_sql.parse(d_cid,d_str,dbms_sql.v7);
dbms_sql.define_column(d_cid,1,old_value,50);
dbms_sql.define_column(d_cid,2,new_value,50);
d_exe := dbms_sql.execute(d_cid);
loop
if dbms_sql.fetch_rows(d_cid) = 0 then
exit;
end if;
dbms_sql.column_value(d_cid,1,old_value);
dbms_sql.column_value(d_cid,2,new_value);
end loop;
dbms_sql.close_cursor(d_cid);
if old_value <> new_value then
insert into ak_aud values(:old.id,r1.col_name,old_value,new_value);
end if;
END LOOP;
END;
/
-
Hi,
you cannot use the :new. and :old. reference in dynamic sql as it is a notation for bind variables.
I don't think this way is possible. You will have to "hardcode" the test for each column in the table and modify the trigger if structure of the table changes.
Ales
[Edited by ales on 05-15-2002 at 09:23 AM]
-
Dear Ales,
The hardcoding will not be effective if the table is altered.
So that is not a good practice.
Can you suggest some other way out ??
Thanks
-
hi
u hv to do something like this
i this example the trigger update the audit table where therez an update or delete in the REF_SEC_SALE table
CREATE OR REPLACE TRIGGER T_U_D_REF_SEC_SALE
AFTER UPDATE OR DELETE ON REF_SEC_SALE
FOR EACH ROW
DECLARE
--Variable Declaration
lv_aud_seq_no NUMBER;
lv_tab_name VARCHAR2(30) := 'REF_SEC_SALE';
BEGIN
SELECT seq_sec_audit_id.NEXTVAL
INTO lv_aud_seq_no FROM dual;
IF UPDATING THEN
INSERT INTO T_SEC_AUDIT_TRAIL(
AUD_SEQ_NO, TABLE_NAME, COLUMN_NAME, OLD_VALUE,
NEW_VALUE, TRIGGER_FLAG,CREATE_DATE, CREATE_USER)
VALUES (lv_aud_seq_no, lv_tab_name, 'COUNTRYCODE',
:OLD.COUNTRYCODE, :NEW.COUNTRYCODE, 'U', SYSDATE, USER );
IF :NEW.EFFECTIVE_DATE != :OLD.EFFECTIVE_DATE THEN
INSERT INTO T_SEC_AUDIT_TRAIL(
AUD_SEQ_NO, TABLE_NAME, COLUMN_NAME, OLD_VALUE,
NEW_VALUE, TRIGGER_FLAG,CREATE_DATE, CREATE_USER)
VALUES (lv_aud_seq_no, lv_tab_name, 'EFFECTIVE_DATE',
:OLD.EFFECTIVE_DATE, :NEW.EFFECTIVE_DATE, 'U', SYSDATE, USER);
END IF;
IF :NEW.STATUS != :OLD.STATUS THEN
INSERT INTO T_SEC_AUDIT_TRAIL(
AUD_SEQ_NO, TABLE_NAME, COLUMN_NAME, OLD_VALUE,
NEW_VALUE, TRIGGER_FLAG,CREATE_DATE, CREATE_USER)
VALUES (lv_aud_seq_no, lv_tab_name, 'STATUS',
:OLD.STATUS, :NEW.STATUS, 'U', SYSDATE, USER);
END IF;
END IF;
IF DELETING THEN
INSERT INTO T_SEC_AUDIT_TRAIL(
AUD_SEQ_NO, TABLE_NAME, COLUMN_NAME, OLD_VALUE,
NEW_VALUE, TRIGGER_FLAG,CREATE_DATE, CREATE_USER)
VALUES (lv_aud_seq_no, lv_tab_name, 'EFFECTIVE_DATE',
:OLD.EFFECTIVE_DATE, NULL, 'D', SYSDATE, USER);
INSERT INTO T_SEC_AUDIT_TRAIL(
AUD_SEQ_NO, TABLE_NAME, COLUMN_NAME, OLD_VALUE,
NEW_VALUE, TRIGGER_FLAG,CREATE_DATE, CREATE_USER)
VALUES (lv_aud_seq_no, lv_tab_name, 'MATURITY_DATE',
:OLD.MATURITY_DATE, NULL, 'D', SYSDATE, USER);
INSERT INTO T_SEC_AUDIT_TRAIL(
AUD_SEQ_NO, TABLE_NAME, COLUMN_NAME, OLD_VALUE,
NEW_VALUE, TRIGGER_FLAG,CREATE_DATE, CREATE_USER )
VALUES (lv_aud_seq_no, lv_tab_name, 'STATUS',
:OLD.STATUS, NULL, 'D', SYSDATE, USER);
END IF;
END;
rgds,
Cheers!
OraKid.
-
Originally posted by Anomitro
The hardcoding will not be effective if the table is altered.
So that is not a good practice.
Yes, I agree.
In PL/SQL does not exist the access to metadata of a record (except the dbms_sql package). Here I mean that's not possible to get list of column names of a record, you always have to know column names if you write the code.
I find it one of the most annoying features of PL/SQL.
In addition, as I wrote earlier, it's not possible to use :new. and :old. records in dynamic SQL as it always thinks they're bind variables.
Thus, I don't believe there's a solution of your problem.
From my point of view, alteration of the table shouldn't be very frequent and auditing is not a vital issue, so I'd suggest put up with that. Write it in the best way that Oracle makes possible and hardcode it.
And alterations of the table? It's another item to remember. Anyway, there are more things that don't work automatically ;-).
Good luck!
Ales
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|