用存储过程动态创建触发器来记录表数据的更新，巨头疼的问题，高分求解！！！

Question

我从国外网站看到一段代码，很长，大家帮忙看看！！！
/************************************/
1. user who excute that proc has create and alter any trigger system privileges and DBA
2. There isn't any trailing "/" at the end
3. This is my codePROCEDURE CREATE_AUDIT_TRIGGER
 (p_table_name IN VARCHAR2)
AS
 CURSOR c1 IS SELECT * FROM audit_info WHERE table_name = UPPER (p_table_name);
 v_sql            VARCHAR2 (4000) := NULL;
 v_table_name     VARCHAR2 (30)   := NULL;
 cid2             INTEGER;
 result_set       INTEGER;
 column_fix      VARCHAR2(200);
 value_fix      VARCHAR2(200);
 column_list      VARCHAR2(200);
 value_list      VARCHAR2(200);
BEGIN
 v_table_name := RTRIM(LTRIM(SUBSTR(RPAD(p_table_name, 30),1,21)));
 cid2 := DBMS_SQL.OPEN_CURSOR;  
 v_sql := 
     'CREATE OR REPLACE TRIGGER ' || v_table_name || '_audit_trig' || CHR(13)|| CHR(10)
 ||  ' AFTER INSERT OR DELETE OR UPDATE ON ' || v_table_name || CHR(13)|| CHR(10)
 ||  ' FOR EACH ROW' || CHR(13)|| CHR(10)
 ||  ' DECLARE' || CHR(13)|| CHR(10)
 ||  ' s_id VARCHAR2(10);' || CHR(13)|| CHR(10)
 ||  ' usr VARCHAR2(20);' || CHR(13)|| CHR(10)
 ||  ' BEGIN ' || CHR(13)|| CHR(10)
 ||  ' s_id:=AUDIT_PKG.GET_SESSION_ID;' || CHR(13)|| CHR(10)
 ||  ' usr:=AUDIT_PKG.GET_USER_NAME;' || CHR(13)|| CHR(10);
 column_fix:='INSERT INTO audit_log(rec_id,table_name,session_id,user_name,action_date,action';
 for rec in c1 loop
 column_list:='';
 value_list:='';
 if rec.key_column_1 is not null then
   column_list:=column_list || ',key_column_1'; 
   value_list:=value_list || ',' || ' :NEW.' || rec.key_column_1 ;
 end if;
 if rec.key_column_2 is not null then
   column_list:=column_list || ',key_column_2'; 
   value_list:=value_list || ',' || ' :NEW.' || rec.key_column_2;
 end if;
 if rec.key_column_3 is not null then
   column_list:=column_list || ',key_column_3'; 
   value_list:=value_list || ',' || ' :NEW.' || rec.key_column_3;
 end if;
 if rec.c_column_1 is not null then
   column_list:=column_list || ',c_column_1'; 
   value_list:=value_list || ',' || ' :NEW.' || rec.c_column_1;
 end if;
 if rec.c_column_2 is not null then
   column_list:=column_list || ',c_column_2'; 
   value_list:=value_list || ',' || ' :NEW.' || rec.c_column_2;
 end if;
   if rec.c_column_3 is not null then
   column_list:=column_list || ',c_column_3'; 
   value_list:=value_list || ',' || ' :NEW.' || rec.c_column_3;
 end if;
   if rec.c_column_4 is not null then
   column_list:=column_list || ',c_column_4'; 
   value_list:=value_list || ',' || ' :NEW.' || rec.c_column_4;
 end if;
if rec.c_column_5 is not null then
   column_list:=column_list || ',c_column_5'; 
   value_list:=value_list || ',' || ' :NEW.' || rec.c_column_5;
 end if;
 if rec.n_column_1 is not null then
   column_list:=column_list || ',n_column_1'; 
   value_list:=value_list || ',' || ' :NEW.' || rec.n_column_1;
 end if;
 if rec.n_column_2 is not null then
   column_list:=column_list || ',n_column_2'; 
   value_list:=value_list || ',' || ' :NEW.' || rec.n_column_2;
 end if;
   if rec.n_column_3 is not null then
   column_list:=column_list || ',n_column_3'; 
   value_list:=value_list || ',' || ' :NEW.' || rec.n_column_3;
 end if;
   if rec.n_column_4 is not null then
   column_list:=column_list || ',n_column_4'; 
   value_list:=value_list || ',' || ' :NEW.' || rec.n_column_4;
 end if;
   if rec.d_column_1 is not null then
   column_list:=column_list || ',d_column_1'; 
   value_list:=value_list || ',' || ' :NEW.' || rec.d_column_1;
 end if;
 if rec.d_column_2 is not null then
   column_list:=column_list || ',d_column_2'; 
   value_list:=value_list || ',' || ' :NEW.' || rec.d_column_2;
 end if;
 if rec.d_column_3 is not null then
   column_list:=column_list || ',d_column_3'; 
   value_list:=value_list || ',' || ' :NEW.' || rec.d_column_3;
 end if;
 exit;
 end loop;
 
 for rec in c1 loop
     if rec.audit_insert is not null then
         value_fix:='VALUES(audit_seq.nextval,'''|| v_table_name ||''',s_id,usr,SYSDATE,''I''';
         v_sql:=v_sql || 'IF INSERTING THEN '|| CHR(13)|| CHR(10);
         v_sql:=v_sql || column_fix || column_list || ') ';
         v_sql:=v_sql || value_fix || value_list || '); ' || CHR(13)|| CHR(10);
         v_sql:=v_sql || 'END IF;' || CHR(13)|| CHR(10);
     end if;
     if rec.audit_delete is not null then
         value_fix:='VALUES(audit_seq.nextval,'''|| v_table_name ||''',s_id,usr,SYSDATE,''D''';
         v_sql:=v_sql || 'IF DELETING THEN '|| CHR(13)|| CHR(10);
         v_sql:=v_sql || column_fix || column_list || ') ';
         v_sql:=v_sql || REPLACE(value_fix || value_list || '); ',':NEW.',':OLD.') || CHR(13)|| CHR(10);
         v_sql:=v_sql || 'END IF;'|| CHR(13)|| CHR(10);
     end if;
     if rec.audit_update is not null then
         value_fix:='VALUES(audit_seq.nextval,'''|| v_table_name ||''',s_id,usr,SYSDATE,''O''';
         v_sql:=v_sql || 'IF UPDATING THEN '|| CHR(13)|| CHR(10);
         v_sql:=v_sql || column_fix || column_list || ') ';
         v_sql:=v_sql || value_fix || value_list || '); '|| CHR(13)|| CHR(10);
         value_fix:='VALUES(audit_seq.currval,'''|| v_table_name ||''',s_id,usr,SYSDATE,''N''';
         v_sql:=v_sql || column_fix || column_list || ') ';
         v_sql:=v_sql || REPLACE(value_fix || value_list || '); ',':NEW.',':OLD.')|| CHR(13)|| CHR(10);
         v_sql:=v_sql || 'END IF;'|| CHR(13)|| CHR(10);
     end if;
     exit;
 end loop;
 v_sql := v_sql  ||  'END;';
 DBMS_SQL.PARSE(cid2,v_sql,DBMS_SQL.NATIVE);
 result_set := DBMS_SQL.EXECUTE (cid2);
 DBMS_SQL.CLOSE_CURSOR (cid2);
END;/*************************/

Answer 1

create procedure p_name(p_table in varchar2)
as
str varchar2(100);
begin
str:='create trigger name_tri 
      before insert or update '||p_table||
      'for each row
      begin
      if inserting then
      ....  --动态sql
      else
      ....  --动态sql
      end if;
      end';
execute immediate str;
end;
/

Answer 2

To:beckhambobo(beckham) 你的代码我很容易理解，关键是我要通过游标动态的取得字段的名称，这个是最复杂的....

Answer 3

我觉得你的想法可以实现啊。就象你贴的代码那样，先通过动态游标，取得所有字段名称，然后拼出column_list和value_list字串，不过，如果无法用column_list与value_list来记录更新，就用数组把字段名称保留前来，然后再拼出创建Trigger的动态SQL。

Answer 4

将变更信息记录在原表名+_history表中，历史表中与原表字段名一致，增加
变更时间，变更类型
create or replace procedure p_name(
  p_owner in varchar2,
  p_table in varchar2
)
as
  v_string0 varchar2(1000);
  v_string1 varchar2(500);
  v_string2 varchar2(500);
  v_type varchar2(10);
  CURSOR c_fields IS
    select data_type,data_length,column_name 
     from sys.all_tab_columns
    where owner=p_owner and table_name = p_table;
  v_sid    number;
  v_nfield number;
  cursor_name integer;
  v_rows      integer;
begin
  v_string0:=
'create or replace trigger '||p_table||'_audit_trig 
before insert or update of ';
  v_nfield := 0;
  for v_fields in c_fields loop
    if v_nfield>0 then
      v_string0 := v_string0||',';
    end if;
    v_string0 := v_string0||v_fields.column_name;
    v_nfield := v_nfield+1;
  end loop;
  v_string0 := v_string0||' on '||p_table||'
for each row
declare
begin
';
  v_string1 := '  insert into '||p_table||'_history(';
  v_string2 := ' )
    values (';
  v_nfield := 0;
  for v_fields in c_fields loop
    if v_nfield>0 then
      v_string1 := v_string1||',';
      v_string2 := v_string2||',';
    end if;
    v_string1 := v_string1||v_fields.column_name;
    v_string2 := v_string2||':new.'||v_fields.column_name;
    v_nfield := v_nfield+1;
  end loop;
  if inserting then
    v_type := 'insert';
  else
    v_type := 'update';
  end if;
  if v_nfield>0 then
    v_string1 := v_string1||',';
    v_string2 := v_string2||',';
  end if;
  v_string1 := v_string1||'modify_date,modify_type';
  v_string2 := v_string2||'sysdate,'''||v_type||'''';
  v_string0 := v_string0||v_string1||v_string2||');
end;';
  cursor_name := dbms_sql.open_cursor;
  DBMS_SQL.PARSE(cursor_name, v_string0, DBMS_SQL.NATIVE);
  v_rows := dbms_sql.execute(cursor_name);
  DBMS_SQL.close_cursor(cursor_name);
end;
/

Answer 5

select column_name from all_col_comments where table_name='TableName';
得到列名，然后配合动态SQL实现。