Home » SQL & PL/SQL » SQL & PL/SQL » Error in Trigger
Error in Trigger [message #36797] Fri, 21 December 2001 06:37 Go to next message
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 Go to previous message
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

----------------------------------------------------------------------
Previous Topic: The ' Char in Oracle
Next Topic: Re: Reading Text File to Oracle Table by PL/SQL Code
Goto Forum:
  


Current Time: Thu Mar 28 14:55:21 CDT 2024