我现在做一个存储过程,要求输入参数为该表的名称,创建一个触发器,该触发器将记录该表所有字段的Insert和Update的更新情况。该触发器为行级触发器。我觉得这个触发器不难写,但是要动态创建,需要获得表的所有字段名称是个难点。我设想应该从存储过程传入的表名称来获得字段名称:
--定义游标
CURSOR c_fileds IS
select column_name
from sys.all_tab_columns
where (TABLE_NAME = mytablename);
从游标中取得字段名称,存放在数组中,然后,写触发器的内容.....
我想到的就这些了,请高手指点!!!!
分不够的话,再加!!!
--定义游标
CURSOR c_fileds IS
select column_name
from sys.all_tab_columns
where (TABLE_NAME = mytablename);
从游标中取得字段名称,存放在数组中,然后,写触发器的内容.....
我想到的就这些了,请高手指点!!!!
分不够的话,再加!!!
解决方案 »
- 求一条sql语句查两个表的列是否一致
- oracle中 如何查询10天前的数据
- 请问在哪里写存储过程?在哪里执行?请给一个最简单的例子,不用带参数!
- 如何恢复被卸载的数据库实例?
- 关于group by语句的问题,请进
- 谁有PL/SQL的文档资料?
- 高手,,,韦什莫使用了Group by后,有些字段无法获取?
- 这或许对你们来说是容易的,可是我真的不知道怎么办,请大家帮助我。谢谢
- Io 异常: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=169870080)(ERR=12505
- oracle数据库导出
- 请问高人急手的问题(火上房了)如果定义每月每底时间
- PL_SQL存储过程里面写EMAIL发送问题
/************************************/
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;/*************************/