本次需要批量编写监控全数据库表的trigger,记录下变更的字段名和变更的值、表名、相对应的主键和做的操作(增删改)。在自动生成脚本sql后,执行execute immediate sql_str --date:2021-11-05 通
本次需要批量编写监控全数据库表的trigger,记录下变更的字段名和变更的值、表名、相对应的主键和做的操作(增删改)。在自动生成脚本sql后,执行execute
immediate sql_str --date:2021-11-05
(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;
--结束END--
本文标题: 在plsql字符串拼接,批量生成trigger
本文链接: https://www.lsjlt.com/news/8871.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-06-05
2024-06-04
2024-06-04
2024-06-03
2024-06-03
2024-06-03
2024-06-04
2024-06-03
2024-06-03
2024-06-03
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0