ITPub博客

首页 > 数据库 > 数据库开发技术 > TRIGGER里的动态SQL

TRIGGER里的动态SQL

原创 数据库开发技术 作者:zhyuh 时间:2006-12-22 15:37:19 0 删除 编辑

要写一个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 过程已成功完成。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/207/viewspace-885826/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
  • 博文量
    233
  • 访问量
    2012660