TRIGGER裡的動態SQL

要寫一個TRIGGER，把一張表的所有列，修改前後的值，都記錄到另外一張修改歷史表中。想從user_tab_columns裡動態地獲取列名，然後用迴圈自動獲取每一列的修改前後值。於是寫了一個trigger（僅以scott.emp表的sal列為例）

[@more@]

CREATE OR REPLACE TRIGGER Print_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON Emp
FOR EACH ROW
WHEN (new.Empno > 0)
DECLARE
cursor cur_emp is select * from user_tab_columns
where table_name='EMP';
col_name varchar2(20);
sal_new number;
state1 varchar2(500);
cursor_handle integer;
execute_sql integer;
BEGIN
for c1 in cur_emp loop
exit when cur_emp%NOTFOUND;
col_name := c1.column_name;
if (col_name='SAL') then
dbms_output.put_line(col_name);
state1 := 'sal_new := :new.'||col_name;
dbms_output.put_line(state1);
execute immediate state1;
dbms_output.put_line(to_char(sal_new));
end if;
end loop;
END;

雖然編譯透過，但是執行的時候總是報錯。後來查資料才知道動態SQL裡:new.column_name / :old.column_name這種方式根本是不允許的。所以只能寫硬程式碼了。如果有大量的類似trigger，或者表的欄位很多，這都是繁瑣有無趣的工作。

在網上找到一個儲存過程，可以動態地幫助生成這些包含硬程式碼的trigger，也算是一個解決辦法。

儲存過程指令碼：

create or replace procedure p_gen_archive_trigger(
p_base_table_alias in user_triggers.trigger_name%type,
p_base_table_name in user_tables.table_name%type,
p_hist_table_name in user_tables.table_name%type default null)
authid current_user
is
cursor csrColumnsBase is
select lower(column_name) column_name
from user_tab_columns
where table_name = upper(p_base_table_name)
order by column_id;

v_trigger_name user_triggers.trigger_name%type := lower(p_base_table_alias) || '_archive';
v_hist_table_name user_tables.table_name%type := lower(nvl(p_hist_table_name, p_base_table_name || '_history'));
begin
dbms_output.put_line('create or replace trigger ' || v_trigger_name);
dbms_output.put_line(' after insert or update or delete');
dbms_output.put_line(' on ' || lower(p_base_table_name));
dbms_output.put_line(' for each row');
dbms_output.put_line('declare');
dbms_output.put_line(' v_row ' || lower(v_hist_table_name) || '%rowtype;');
dbms_output.put_line('begin');
dbms_output.put_line(' if inserting then');
dbms_output.put_line(' v_row.action_code := ''I'';');
dbms_output.put_line(' elsif updating then');
dbms_output.put_line(' v_row.action_code := ''U'';');
dbms_output.put_line(' elsif deleting then');
dbms_output.put_line(' v_row.action_code := ''D'';');
dbms_output.put_line(' end if;');
dbms_output.put_line('');
dbms_output.put_line(' if inserting');
dbms_output.put_line(' or updating then');
dbms_output.put_line('');

for r in csrColumnsBase loop
dbms_output.put_line( ' ' || 'v_row.' || r.column_name || ' := :new.' || r.column_name || ';');
end loop;

dbms_output.put_line('');
dbms_output.put_line(' else');
dbms_output.put_line('');

for r in csrColumnsBase loop
dbms_output.put_line( ' ' || 'v_row.' || r.column_name || ' := :old.' || r.column_name || ';');
end loop;

dbms_output.put_line('');
dbms_output.put_line(' end if;');
dbms_output.put_line('');
dbms_output.put_line(' insert');
dbms_output.put_line(' into ' || lower(v_hist_table_name));
dbms_output.put_line(' (');

for r in csrColumnsBase loop
dbms_output.put_line( ' ' || r.column_name || ',');
end loop;

dbms_output.put_line( ' ' || 'useracct,');
dbms_output.put_line( ' ' || 'date_time,');
dbms_output.put_line( ' ' || 'action_code)');

dbms_output.put_line(' values(');

for r in csrColumnsBase loop
dbms_output.put_line( ' ' || 'v_row.' || r.column_name || ',');
end loop;

dbms_output.put_line(' user,');
dbms_output.put_line(' sysdate,');
dbms_output.put_line(' v_row.action_code);');
dbms_output.put_line('');
dbms_output.put_line('end;');
dbms_output.put_line('/');
dbms_output.put_line('');

end;
/

執行過程如下：

SQL> set serveroutput on
SQL> exec p_gen_archive_trigger('emp_trigg1','emp','emp_history');
create or replace trigger emp_trigg1_archive
after insert or update or delete
on emp
for each row
declare
v_row emp_history%rowtype;
begin
if inserting then
v_row.action_code := 'I';
elsif updating then
v_row.action_code := 'U';
elsif deleting then
v_row.action_code := 'D';
end if;
if inserting
or updating then
v_row.empno := :new.empno;
v_row.ename := :new.ename;
v_row.job := :new.job;
v_row.mgr := :new.mgr;
v_row.hiredate := :new.hiredate;
v_row.sal := :new.sal;
v_row.comm := :new.comm;
v_row.deptno := :new.deptno;
else
v_row.empno := :old.empno;
v_row.ename := :old.ename;
v_row.job := :old.job;
v_row.mgr := :old.mgr;
v_row.hiredate := :old.hiredate;
v_row.sal := :old.sal;
v_row.comm := :old.comm;
v_row.deptno := :old.deptno;
end if;
insert
into emp_history
(
empno,
ename,
job,
mgr,
hiredate,
sal,
comm,
deptno,
useracct,
date_time,
action_code)
values(
v_row.empno,
v_row.ename,
v_row.job,
v_row.mgr,
v_row.hiredate,
v_row.sal,
v_row.comm,
v_row.deptno,
user,
sysdate,
v_row.action_code);
end;
/

PL/SQL 過程已成功完成。