Error in Trigger [message #36797] |
Fri, 21 December 2001 06:37 |
Bhagwan Singh
Messages: 23 Registered: December 2001
|
Junior Member |
|
|
Hello,
Iam trying to create a dynamic trigger which will take dynamically take column names from a table and concat it with :OLD and :NEW bind varibles.
But it gives me an error and the moment I update any record in emp table , oracle application hangs up and the trigger isn't fired.
My code is as below:
-- I have considered Scott/Tiger example
CREATE OR REPLACE TRIGGER Test_Proc
AFTER UPDATE ON emp
FOR EACH ROW
DECLARE
TYPE str_type IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
arr STR_TYPE;
CURSOR c1 IS SELECT CNAME FROM COL WHERE TNAME='EMP';
j NUMBER(1) :=0 ;
val NUMBER(1) :=0 ;
cname VARCHAR2(60);
BEGIN
-- Fills the Columns in the Array
FOR i IN c1 LOOP
arr(j) := i.cname;
j := j+1;
END LOOP;
val := (arr.COUNT)-1;
WHILE val >= 0
LOOP
-- Here Iam checking for the particular column which is being updated and thereby concatenating with
-- the oracle bind variable
IF UPDATING(arr(val)) THEN
-- emp_audit is a single column table where Iam trying to insert old value of column affected.
EXECUTE IMMEDIATE 'INSERT INTO emp_audit VALUES(:OLD.'||arr(val) ||')';
END IF;
val := val -1;
END LOOP;
END;
Is there any way by which I can dynamically trap the column names?Please let me know if you have queries, if any.
Regards,
Bhagwan Singh Mer
Analyst Programmer
----------------------------------------------------------------------
|
|
|
Re: Error in Trigger [message #36800 is a reply to message #36797] |
Fri, 21 December 2001 11:07 |
Suresh Vemulapalli
Messages: 624 Registered: August 2000
|
Senior Member |
|
|
i think it is not possible because when you use :old.field inside execute immediate statement oracle expecting value with USING clause.
your EI statement should be like below..
execute immediate 'insert into emp_audit values(:old.ename)' using 'ROBERT'
assume 'ROBERT' is :old.ename value
if you can construct like above, it will give proper result.
HTH
Suresh Vemulapalli
----------------------------------------------------------------------
|
|
|