Convert the new row to XML inside a Oracle Trigger

Question

Hi I am trying to create a log of changes in a table by using oracle triggers

The log table consist of the ,time of action ,tablename,actiontype and xmldata (clob)

Iam trying to convert the new row to xml and save it as xmldata

 create or replace TRIGGER EVAL_CHANGE_TriggerActual_DYNAMIC
  AFTER INSERT OR UPDATE OR DELETE
  ON PROJ_TEST
  REFERENCING NEW AS new OLD AS old
 FOR EACH ROW
DECLARE
  log_action  varchar(10);
 p_xmldata     XMLtype;
  P_NEWROWDATA    clob;
  p_newrowxml       clob;  
BEGIN

  select rtrim(xmlelementcol,',') into p_newrowxml from (  Select LISTAGG(str, '') as xmlelementcol from (select  'XMLElement("'||cols.column_Name||'", :NEW.'||cols.column_name||'),' as str 
              from SYS.ALL_TAB_COLS cols where upper(cols.owner)=upper('DEV_CUSTOM')  and upper(cols.table_name)=upper('PROJ_TEST')   order by column_id )); 
             p_newrowxml:=CONCAT('select XMLElement("ResearchTable",',p_newrowxml); 
             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();  

 
  IF INSERTING THEN
    log_action := 'Insert';
  ELSIF UPDATING THEN
    log_action := 'Update';
  ELSIF DELETING THEN
    log_action := 'Delete';
  ELSE
    DBMS_OUTPUT.PUT_LINE('This code is not reachable.');
  END IF;
 INSERT INTO audits(table_name, transaction_name, by_user, transaction_date,xmldata,TRIGGERNAMEdesc)
   VALUES('PROJ_TEST', log_action, USER, SYSDATE,p_newrowdata,'EVAL_CHANGE_TriggerDynamic');
END;

Now if If i remove the below code

'XMLElement("'||cols.column_Name||'", :NEW.'||cols.column_name||'),' 

to

'XMLElement("'||cols.column_Name||'", 1),' 

Its working otherwise not thowing error at EXECUTE IMMEDIATE. Can any one help

Answer 1

while my first answer works, it would depend heavily on table-columns, not really usable if many tables have to be audited. Based on a comment from @Sayan Malakshinov and the linked artikle StevenFeuerstein&CompoundTrigger. the final audit-loop might cause performance-trouble if many rows are processed in one update .... mass-update could be moved to a more optimized update-compound-trigger writing all update-rows with one single "insert into tblaudit() select auditColumns from tblone where id in (recorded id´s)"

-- prepare test-scenario 

CREATE TABLE TBLONE
(
  ID                   NUMBER(19),
  POSTALCODE              VARCHAR2(20 CHAR),
  STREET                  VARCHAR2(255 CHAR),
  HOUSENUMBER             VARCHAR2(25 CHAR),
  CITY                    VARCHAR2(255 CHAR)
)
;


ALTER TABLE TBLONE ADD (
  PRIMARY KEY
  (ID)
  USING INDEX);

insert into tblone values (1,'123123','street1','1a','DevVille1');
insert into tblone values (2,'345','street2','2b','DevVille2');
insert into tblone values (3,'678','street3','3c','DevVille3');


CREATE TABLE TBLAUDIT
(
  ID                   NUMBER(19),
  log_action          VARCHAR2(10 CHAR),
  log_user          varchar(100 char),
  log_data            clob
)
;
-- ... timestamp of modification might be missing ...

CREATE OR REPLACE TRIGGER trg_audit_tblone    
FOR UPDATE OR INSERT OR DELETE ON tblone    
COMPOUND TRIGGER     
   TYPE id_rt IS RECORD (    
      id_column   NUMBER(19) -- assuming all primary-keys are of same type ....
      ,log_action  varchar(10)
   );    
    
   TYPE row_level_info_t IS TABLE OF id_rt  INDEX BY PLS_INTEGER;    
    
   g_row_level_info   row_level_info_t;    
    
   AFTER EACH ROW IS    
   BEGIN  
      g_row_level_info (g_row_level_info.COUNT + 1).id_column := :NEW.id;   -- store primary key only
      -- remember the type of trigger-action
      IF INSERTING THEN
        g_row_level_info (g_row_level_info.COUNT).log_action := 'Insert';
  ELSIF UPDATING THEN
        g_row_level_info (g_row_level_info.COUNT).log_action := 'Update';
  ELSIF DELETING THEN
        g_row_level_info (g_row_level_info.COUNT).log_action := 'Delete';
   end if;
       
   END AFTER EACH ROW;    
    
   AFTER STATEMENT IS    
      l_rowdata    clob; 
   BEGIN      
      -- for all row-actions recorded 
      FOR indx IN 1 .. g_row_level_info.COUNT    
      LOOP          
        if g_row_level_info (indx).log_action= 'Delete' then
            -- no row selectable, direct insert to audit
            insert into TBLAUDIT values(g_row_level_info(indx).id_column,sys_context('USERENV','SESSION_USER') , g_row_level_info (indx).log_action,null);
        else
            select xmltype(cursor(select * from tblone where id=g_row_level_info (indx).id_column)).getclobval() into l_rowdata from dual;
            insert into TBLAUDIT values(g_row_level_info(indx).id_column,sys_context('USERENV','SESSION_USER') , g_row_level_info (indx).log_action,l_rowdata);
        end if;
      END LOOP;    
   END AFTER STATEMENT;    
END trg_audit_tblone;     


update tblone
set street='newStreet'
where id=1;

select id,log_action,log_user,dbms_lob.substr(log_data,200) from tblaudit;


update tblone
set street='newStreet';

select id,log_action,log_user,dbms_lob.substr(log_data,200) from tblaudit;

delete from tblone where id=2;

select id,log_action,log_user,dbms_lob.substr(log_data,200) from tblaudit;

In case update with many-rows have really to be considered:

create TYPE type_audit_entry as object (    
      id_column   NUMBER(19) -- assuming all primary-keys are of same type ....
      ,log_action  varchar(10)
   );

create type typeTable_audit as table of type_audit_entry;


CREATE OR REPLACE TRIGGER trg_audit_update_tblone    
FOR UPDATE ON tblone    
COMPOUND TRIGGER     
g_row_level_info typeTable_audit := typeTable_audit();

    BEFORE STATEMENT IS
     BEGIN
       g_row_level_info:=typeTable_audit();
     END BEFORE STATEMENT;
    
    
   AFTER EACH ROW IS    
   BEGIN  
        g_row_level_info.extend;
        g_row_level_info (g_row_level_info.COUNT):=type_audit_entry(:NEW.id,null);
   END AFTER EACH ROW;    
    
   AFTER STATEMENT IS    
      l_rowdata    clob; 
   BEGIN      
      insert into TBLAUDIT(ID,log_action,log_user,log_time,log_data)
      select stored_audit.id_column,'Update',sys_context('USERENV','SESSION_USER'),sysdate,xmltype(cursor(select * from tblone where id=stored_audit.id_column)).getclobval()
      from TABLE(g_row_level_info) stored_audit;
   END AFTER STATEMENT;    
END trg_audit_update_tblone;  

delete from tblaudit;

update tblone
set street='newStreet';

select id,log_action,log_time,log_user,dbms_lob.substr(log_data,200) from tblaudit;

Answer 2

On of the pitfalls in triggers is: :new / :old are very trigger specific and tedious to handle. one way to workaround would be explicitely constructing the xmltype:

create or replace TRIGGER EVAL_CHANGE_TRIGGER
  AFTER INSERT OR UPDATE OR DELETE
  REFERENCING NEW AS NEW OLD AS OLD
  ON ResearchTable
DECLARE
  log_action  varchar(10);
  p_xmldata     XMLtype;
  p_newrowdata    clob;
BEGIN
    select XMLElement("ResearchTable", 
                   XMLElement("myColumn1", :NEW.myColumn1),
                   XMLElement("myColumn2", :NEW.myColumn2),
                   ....)
                   into p_xmldata from dual;
                   
   p_newrowdata:=p_xmldata.getClobVal();  
  
  IF INSERTING THEN
    log_action := 'Insert';
  ELSIF UPDATING THEN
    log_action := 'Update';
  ELSIF DELETING THEN
    log_action := 'Delete';
  ELSE
    DBMS_OUTPUT.PUT_LINE('This code is not reachable.');
  END IF;

 INSERT INTO auditsResearch (table_name, transaction_name, by_user, transaction_date,XMLDATA)
   VALUES('PROJ_TEST', log_action, USER, SYSDATE,p_newrowdata);
END;

The xml-generation is very table-specific (because of :new - limitations), i would use some code-generation by querying the meta-data of the target table:

select 'XMLElement("'||cols.column_Name||'", :NEW.'||cols.column_name||'),'
 from SYS.ALL_TAB_COLS cols
where upper(cols.owner)=upper('MY_TARGET_SCHEMA')
and upper(cols.table_name)=upper('MY_TABLE')
order by column_id
;

Answer 3

There is one simple method to convert whole row or even recordset to xml: you can just wrap cursor and pass it into XMLType as input parameter. For example:

select xmltype(cursor(select * from test where N=1)) from dual;

PL/SQL example:

declare x xmltype;
begin
   select xmltype(cursor(select * from test where N=1))
     into x
   from dual;
   dbms_output.put_line(x.getclobval());
end;
/