在 Oracle 触发器中将新行转换为 XML [英] Convert the new row to XML inside a Oracle Trigger

问题描述

您好，我正在尝试使用 oracle 触发器在表中创建更改日志

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

日志表由动作时间、表名、动作类型和xmldata(clob)组成

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

我正在尝试将新行转换为 xml 并将其另存为 xmldata

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||'),' 

到

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

否则它不会在立即执行时抛出错误.谁能帮忙

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

推荐答案

虽然我的第一个答案有效，但它在很大程度上依赖于表列，如果必须审核许多表，则不太可用.基于@Sayan Malakshinov 的评论和链接的文章 StevenFeuerstein&CompoundTrigger.如果在一次更新中处理许多行，最终的审计循环可能会导致性能问题……可以将批量更新移动到更优化的更新复合触发器，将所有更新行写入 tblaudit 中的单个插入"() select auditColumns from tblone where id in (recorded id´s)"

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;

这篇关于在 Oracle 触发器中将新行转换为 XML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！