在触发器内部使用OLD和NEW对象进行动态操作 [英] Using OLD and NEW object for dynamic operations inside trigger

问题描述

我想知道是否可以在触发器内部使用OLD和NEW对象进行动态操作.

I want to know whether I can use the OLD and NEW objects for dynamic operations inside trigger.

我正在寻找的东西是这样的:-

What I am looking for is something like this :-

1. ABC是我需要为其编写触发器的表.
2. TracK_Table维护需要跟踪(记录)的表的列列表.

3. f_log是一个将数据更改插入到跟踪(日志)表中的函数.

1. ABC is a table for which I need to write Trigger.
2. TracK_Table maintains list of columns for table which need to be tracked (logged).

3. f_log is a function that inserts changes in data into a tracking(log) table.

CREATE OR REPLACE TRIGGER trg_TRACK
AFTER INSERT OR UPDATE OR DELETE ON ABC
FOR EACH ROW
declare
    v_old_val varchar2(1000);
    v_new_val varchar2(1000);
    n_ret int;
    n_id varchar(50);

    cursor cur_col is 
    SELECT  COLUMN_NAME,
        TABLE_name      
    FROM    track_TABLE
    WHERE   upper(TABLE_NAME) = upper('ABC') 
      AND exists (select  cname 
                    from col
                   where UPPER(tname) =upper('ABC') 
                     and upper(cname)=upper(COLUMN_NAME))
      AND upper(allow) = 'Y';

begin
   n_id:= :old.id;

   for i_get_col in c_get_col
   loop
      execute immediate 
         'begin 
            :v_old_val:= select '||i_get_col.column_name ||' 
                           from '||:old ||' 
                          where id = '||n_id ||'; 
          end;' using out v_old_val;
      execute immediate 
           'begin 
             :v_new_val:= select '||i_get_col.column_name ||' 
                            from '||:new ||' 
                            where id = '||n_id ||'; 
            end;' using out v_new_val;
      n_ret := f_log(n_id,i_get_col.column_name,v_old_val,v_new_val);
   end loop;
end;
/

推荐答案

一个选项:推送逻辑以检查f_log过程中是否正在跟踪一列，然后遍历所有列.

One Option: Push the logic to check if a column is being tracke into the f_log procedure and then pass across all of the columns.

例如，如果您的track_Table拥有要跟踪的每一列的(table_name，column_name，allow)值，则类似这样

For example, if your track_Table holds (table_name, column_name, allow) values for each column that you want to trackm then something like this

 CREATE OF REPLACE PROCEDURE f_log(  p_id          varchar2  
                                    ,p_table_name  varchar2
                                    ,p_column_name varchar2
                                    ,p_old_val     varchar2
                                    ,p_new_val     varchar2)
 as
    l_exists number;
    cursor chk_column_track IS
        SELECT  1
        FROM    track_TABLE           
        WHERE   upper(TABLE_NAME)  = upper(p_table_name)              
        AND     UPPER(column_name) = upper(p_column_name)
        AND     upper(allow) = 'Y'; 
 begin
    open chk_column_track;
    fetch chk_column_track into l_exists;
    if chk_column_track%found then
       --do the insert here
    end if;
    close chk_column_track;
 end;
 /

 CREATE OR REPLACE TRIGGER trg_TRACK 
 AFTER INSERT OR UPDATE OR DELETE ON ABC 
 FOR EACH ROW 
 DECLARE   
      n_id varchar(50); 
 BEGIN   
    n_id := NVL(:old.id, :new.id);   
    -- send all of the values to f_log and have it decide whether to save them
    f_log(:old.id,'COL1',:old.col1,:new.col1);   
    f_log(:old.id,'COL2',:old.col2,:new.col2);   
    f_log(:old.id,'COL3',:old.col3,:new.col3);   
    ... 
 END; 

为了方便起见，在插入时将track_table中的值大写，这样您就不必UPPER()存储的值，从而使这些值上的任何索引都变得毫无用处！

And for goodness sake, upper-case the values in your track_table on insert so that you don't have to UPPER() the stored values thus making any index on those values useless!

现在，这将占用一些资源，以检查每个操作上的每个列名称，但是如果您不运行大量文件，那么它可能是可管理的.

Now, this will chew up some resources checking each column name on each operation, but if you are not running high-volumes then it might be manageable.

否则，您将需要一个更优雅的解决方案.就像利用集合和TABLE()子句的功能在批量操作中进行track_table查找一样.请记住，我目前不在数据库中，因此我尚未对该代码进行测试编译.

Otherwise you will need a more elegant solution. Like leveraging the power of collections and the TABLE() clause to do the track_table lookup in a bulk operation. Bear in mind that I am away from my database at the moment, so I have not test-compiled this code.

    CREATE OR REPLACE TYPE t_audit_row AS OBJECT (
   p_table_name   varchar2(30)
  ,p_column_name  varchar2(30)
  ,p_id           varchar2(50)
  ,p_old_val      varchar2(2000)
  ,p_new_val      varchar2(2000)
);

CREATE OR REPLACE TYPE t_audit_row_table AS TABLE OF t_audit_row;

CREATE OR REPLACE PROCEDURE f_log (p_audit_row_table t_audit_Row_table)
AS
begin
   -- see how we can match the contents of the collection to the values
   -- in the table all in one query. the insert is just my way of showing
   -- how this can be done in one bulk operation. Alternately you could make
   -- the select a cursor and loop through the rows to process them individually.
   insert into my_audit_log (table_name, column_name, id, old_val, new_val)
   select  p_table_name
          ,p_column_name
          ,p_id
          ,p_old_val
          ,p_new_val
   FROM   track_TABLE TT
         ,table(p_audit_row_table) art
   WHERE  tt.TABLE_NAME   = art.p_table_name                       
   AND    tt.column_name  = art.p_column_name         
   AND    tt.allow        = 'Y';
end;
/

CREATE OR REPLACE TRIGGER trg_TRACK   
AFTER INSERT OR UPDATE OR DELETE ON ABC   
FOR EACH ROW   
DECLARE          
  l_id           varchar(50);   
  l_audit_table  t_audit_row_table;
BEGIN        
  l_id := NVL(:old.id, :new.id);        
  -- send all of the values to f_log and have it decide whether to save them   
  l_audit_table := t_audit_row_table (
                      t_audit_row ('ABC','COL1',l_id, :old.col1, :new.col1)  
                      ,t_audit_row ('ABC','COL2',l_id, :old.col2, :new.col2)  
                      ,t_audit_row ('ABC','COL3',l_id, :old.col3, :new.col3)  
                      ,...  
                      ,t_audit_row ('ABC','COLn',l_id, :old.coln, :new.coln)  
                   );
  f_log(l_audit_table);
end;
/

这篇关于在触发器内部使用OLD和NEW对象进行动态操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！