I am trying to create a row trigger (after) where I will convert all the :new values inside the row to xml and save it in another table or Queue
I am able to do it if i statically bindevery column name with :New ,but I don't want to statically mention the column names in trigger as column names may change in future
My problem is i cannot bind variables dynamically to :NEW .it gives error when i loop through columns of table and try to get :New. Column Name
create or replace TRIGGER EVAL_CHANGE_TriggerActual_Test
AFTER INSERT OR UPDATE OR DELETE
ON PROJ_TEST
REFERENCING NEW AS new OLD AS old
FOR EACH ROW
DECLARE
p_xmldata XMLtype;
P_NEWROWDATA clob;
p_newrowxml clob;
ccolumn_item varchar(3000);
ccolname varchar(3000);
BEGIN
p_newrowxml:='select XMLElement("ResearchTable",';
FOR somecol IN (select cols.column_name as str from SYS.ALL_TAB_COLS cols where upper(cols.owner)=upper('DEVenv1_CUSTOM') and upper(cols.table_name)=upper('PROJ_TEST') order by column_id)
LOOP
ccolumn_item:='XMLElement("'||somecol.str||'", ' ;
-- ccolumn_item:=CONCAT(ccolumn_item,:NEW.DESCR );
-- This works fine when i try to add columns statically
ccolumn_item:=CONCAT(ccolumn_item,:NEW.'||somecol.str||' );
-- Error in the line here when i dynamically try to get value from new
p_newrowxml:=CONCAT(p_newrowxml,ccolumn_item );
END LOOP;
p_newrowxml:=CONCAT(p_newrowxml,' ) from dual');
DBMS_OUTPUT.PUT_LINE(p_newrowxml);
--EXECUTE IMMEDIATE p_newrowxml into p_xmldata ;
-- p_newrowdata:=p_xmldata.getClobVal();
END;