本次需要批量编写监控全数据库表的trigger，记录下变更的字段名和变更的值、表名、相对应的主键和做的操作（增删改）。在自动生成脚本sql后，执行execute

immediate sql_str --date：2021-11-05
  1. 通过 || 拼接字符串。
  2. 通过user_cons_columns , user_constraints 来获取表的主键。以CONSTRAINT_TYPE 选定主键类型，以TABLE_NAME绑定表名，从而获取表的主键名，为后续拼接获取表主键和主键值做准备。最后，将该表所有主键名存放于var_array （防止组合主键），后续能够遍历取出
  3. 通过表user_tab_columns 获取表所有字段，后续拼接查看值的更新。
  4. 通过new.column <> old.column 来判断前后值是否变更，如若变更，记录下变更的值。
  5. if deleting，inserting，updating来判断执行的操作。
  6. 最后，生成的trigger将向我们新建的存储表插入执行的操作，变更的列名，变更的值以及主键，以便于我们检索。
  7. 同理，可生成批量启动和关闭trigger的脚本。

（1）首先获取数据库中所有表的表名

for tables_name in (
select table_name
from user_tables
) loop

（2）根据约束表，获取各表的主键--需求需要

for primary_key in (
select cu.COLUMN_NAME from user_cons_columns cu , user_constraints au
where cu.constraint_name = au.CONSTRAINT_NAME
and au.CONSTRAINT_TYPE = 'P'
and au.TABLE_NAME= tables_name.table_name
)loop
i:=i+1;
var_array(i):=primary_key.column_name;
end loop;

（3）trigger在插入修改和删除前触发

-- the first set sql_str
sql_str := 'create or replace trigger '||' task_for_link_'||tables_name.table_name||
' before insert or update or delete'||
' on '||tables_name.table_name||
' for each row'||
' declare '||
'jobName varchar2(200);'||
'tableName varchar2(200);'||
'columnName varchar2(200);'||
'str varchar2(2000);'||
'pk_information varchar2(300);'||
' begin '||
'tableName := '''||tables_name.table_name||''';';

for primary_key_count in 1..var_array.count loop
sql_str := sql_str||
'pk_information := '''||var_array(primary_key_count)||'''||:new.'||var_array(primary_key_count)||';';
end loop;
var_array.delete();
i:=0;
sql_str := sql_str||
'if updating then';

 

（4）拼接，判断字段是否有变更，如若有记录下来

-- get every column_name by table_name
for columns_name in (
select t.table_name,t.column_name
from user_tab_columns t
where t.TABLE_NAME = tables_name.table_name
) loop
-- the second set column to sql_str

sql_str := sql_str||
' if :new.'||columns_name.column_name||' <> :old.'||columns_name.column_name||' then'||
' str := str||'''||columns_name.column_name||''';'||
'end if;';
end loop;
-- the third set the whole sql_str
sql_str := sql_str||
' insert into t_task ( JOB_NAME, TABLE_NAME, COLUMN_NAME , pk_information)'||
' values ( ''update'', tableName, str, pk_information);'||
' end if;'||
' if inserting then'||
' insert into t_task ( JOB_NAME, TABLE_NAME, COLUMN_NAME, pk_information)'||
' values ( ''insert'', tableName, columnName, pk_information); '||
' end if;'||
' if deleting then'||
' insert into t_task ( JOB_NAME, TABLE_NAME, COLUMN_NAME, pk_information)'||
' values ( ''delete'', tableName, columnName, pk_information); '||
' end if;'||
'end task_for_link_'||table_name||';';

 

（5）执行脚本--when other then null（抛错继续执行）

begin
execute immediate sql_str;
exception
when others then null;
end;
dbms_output.put_line(sql_str);
end loop;
end;

 

内容来源于网络如有侵权请私信删除

文章来源: 博客园

原文链接: https://www.cnblogs.com/ngnba/p/15512142.html

标签： oracle数据库 orcale开发 oracle技术文章
你还没有登录，请先登录或注册！
  • 还没有人评论，欢迎说说您的想法！