Skip to Main Content
  • Questions
  • Problem in dynamically binding column values in a row trigger

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Sreenath.

Asked: July 16, 2020 - 11:42 am UTC

Last updated: July 20, 2020 - 3:31 am UTC

Version: 18

Viewed 1000+ times

You Asked

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;

and Connor said...

Thats right - you cannot access them dynamically. They are binds not true columns as such.

One way around this is to write code that writes your trigger. This is much better anyway, because then the trigger contains static code (because querying all_tab_cols is expensive).

An example of that here

https://asktom.oracle.com/pls/asktom/asktom.search?tag=generic-trigger-for-auditing-column-level-changes



Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library