TRIG自动处理修改自己TRIG
create or replace procedure "MES_TABEL_LOG"
(tabel_name in varchar2,creat_trigger in integer :=0)
is
PRAGMA AUTONOMOUS_TRANSACTION;--is owen pragram
job binary_integer;
i integer;
m_sql varchar2(4000);
m_sql_col varchar2(4000);
m_sql_value varchar2(4000);
m_sql_filter varchar2(4000);
m_tem varchar2(4000);
m_col varchar2(200);
m_col_type varchar2(50);
m_col_len number;
m_tab1 varchar2(200);
m_tab2 varchar2(200);
v_sqlcode varchar2(100);
v_sqlerrm varchar2(200);
cursor cur_col(tab1 varchar2,tab2 varchar2) is
select COLUMN_NAME from all_tab_columns where TABLE_NAME = tab1
minus select COLUMN_NAME from all_tab_columns where TABLE_NAME = tab2;
cursor cur_col_1(tab1 varchar2) is
select COLUMN_NAME from all_tab_columns where TABLE_NAME = tab1 ORDER BY COLUMN_ID ;
begin
m_tab1:=upper(tabel_name);
m_tab2:=m_tab1||'_LOG';
----э
select count(*) into i from all_tables where table_name=m_tab2;
if i=0 then
m_sql:='create table'|| m_tab2 ||'
( USERID VARCHAR2(7),
COMMANDTYPE VARCHAR2(10),
DATETIME DATE,
OS_USER VARCHAR2(20) default SYS_CONTEXT(''USERENV'' ,''OS_USER''),
OS_IP VARCHAR2(15) default SYS_CONTEXT(''USERENV'',''IP_ADDRESS''),
OS_PC VARCHAR2(20) default SYS_CONTEXT(''USERENV'',''HOST''))';
execute immediate m_sql;
insert into MES_trig_log (Table_Name,Trans_Date,Errcode,Errm)
values(m_tab2,sysdate,'create TABLE',m_sql);
commit;
end if;
open cur_col(m_tab1,m_tab2);
loop
fetch cur_col into m_col;
exit when cur_col%notfound;
select t.DATA_TYPE,t.DATA_LENGTH into m_col_type,m_col_len from all_tab_columns t where t.TABLE_NAME=m_tab1 and t.COLUMN_NAME=m_col;
m_sql:='ALTER TABLE '||m_tab2 ||' ADD ' ||m_col ||' '|| m_col_type;
IF m_col_type='VARCHAR2' then
m_sql:=m_sql||'('||m_col_len||')';
elsif m_col_type='CHAR' then
m_sql:=m_sql||'('||m_col_len||')';
end if;
BEGIN
execute immediate m_sql;
insert into MES_trig_log (Table_Name,Trans_Date,Errcode,Errm)
values(m_tab2,sysdate,'alter table',m_sql);
commit;
END;
end loop;
close cur_col;
if creat_trigger=0 then---create job
m_sql:='MES_TABEL_LOG('''||m_tab1 ||''',true);';
select count(*) into i from all_jobs where what=m_sql;
if i =0 then
sys.dbms_job.submit(job => job,
what => m_sql,
next_date => sysdate+0.0007);---delay 1min run
commit;
end if;
elsif creat_trigger=1 then---эtrig
if m_tab1='EQMS' then
m_sql_filter:='update of "MACH_ID","MACH_DESC","WORK_CTR","FAC_ID","PN_CHK","FAB_INLINE","DELL_LINE","CHECK_HOLD_FLAG","WARN_MSG","HOLD_TIME","PROCESS_TYPE","R_CHECK_HOLD_FLAG","R_WARN_MSG","R_HOLD_TIME","AFTER_NUM","SHIFT_TIME","SCH_QTY" ';
else
m_sql_filter:=' update ';
end if;
m_sql:='create or replace trigger '||m_tab2||'_TRI after insert or
'||m_sql_filter||'
or delete on CPT.'||m_tab1||' for each row
declare i integer;v_userid varchar2(7);v_tab varchar2(50); v_command varchar2(10);v_sqlcode varchar2(100);v_sqlerrm varchar2(2000);
------trig create time '||to_char(sysdate,'yyyy/mm/dd hh24:mi:ss')||'
begin v_tab:='''||m_tab1||'''; -----TABEL CHANGE
begin----GET USER ID
select userid into v_userid from usersessionmap where sessionid=sys.dbms_session.unique_session_id;
exception when others then v_userid:=SYS_CONTEXT(''USERENV'',''SESSION_USERID'');
end;
SELECT COUNT(*) INTO i FROM (select COLUMN_NAME from all_tab_columns where TABLE_NAME = v_tab minus select COLUMN_NAME from all_tab_columns where TABLE_NAME = v_tab'||chr(124)||chr(124)||chr(39)||'_LOG'');
if i>0 then MES_TABEL_LOG(v_tab); end if;
if inserting then v_command:=''insert''; elsif updating then v_command:=''update''; else v_command:=''delete''; end if;
------WRITE DB
if inserting then ';
m_sql_col:='
insert into '||m_tab2||' (';
--m_sql_old:=m_sql_new;
i:=0;
open cur_col_1(m_tab1);
loop
fetch cur_col_1 into m_col;
exit when cur_col_1%notfound;
i:=i+1;
m_sql_col:=m_sql_col||m_col||',';
m_sql_value:=m_sql_value||':new.'||m_col||',';
if i=5 then
m_sql_col:=m_sql_col||'
';
m_sql_value:=m_sql_value||'
';
i:=0;
end if;
end loop;
close cur_col_1;
m_sql_col:=m_sql_col||'userid,datetime,COMMANDTYPE )
values('||m_sql_value||'v_userid,sysdate,v_command);
';
m_tem:=' else'|| replace(m_sql_col,':new.',':old.')||' end if;
exception when others then v_sqlcode:=sqlcode; v_sqlerrm:=sqlerrm;
insert into MES_trig_log (Table_Name,Trans_Date,Errcode,Errm,USERID) values(v_tab,sysdate,v_sqlcode,v_sqlerrm,v_userid);
end '||m_tab1||'_LOG_TRI;';
--execute immediate 'grant create trigger to CPT';
execute immediate m_sql||m_sql_col||m_tem;
commit;
insert into MES_trig_log (Table_Name,Trans_Date,Errcode,Errm)
values(m_tab2,sysdate,'эTRIGGER',''); --write log
commit;
end if;
EXCEPTION
when no_data_found then
null;
WHEN others THEN
v_sqlcode:=sqlcode;
v_sqlerrm:=sqlerrm;
insert into MES_trig_log (Table_Name,Trans_Date,Errcode,Errm) values(m_tab2,sysdate,v_sqlcode,v_sqlerrm);---write error log
commit;
end MES_TABEL_LOG;
(tabel_name in varchar2,creat_trigger in integer :=0)
is
PRAGMA AUTONOMOUS_TRANSACTION;--is owen pragram
job binary_integer;
i integer;
m_sql varchar2(4000);
m_sql_col varchar2(4000);
m_sql_value varchar2(4000);
m_sql_filter varchar2(4000);
m_tem varchar2(4000);
m_col varchar2(200);
m_col_type varchar2(50);
m_col_len number;
m_tab1 varchar2(200);
m_tab2 varchar2(200);
v_sqlcode varchar2(100);
v_sqlerrm varchar2(200);
cursor cur_col(tab1 varchar2,tab2 varchar2) is
select COLUMN_NAME from all_tab_columns where TABLE_NAME = tab1
minus select COLUMN_NAME from all_tab_columns where TABLE_NAME = tab2;
cursor cur_col_1(tab1 varchar2) is
select COLUMN_NAME from all_tab_columns where TABLE_NAME = tab1 ORDER BY COLUMN_ID ;
begin
m_tab1:=upper(tabel_name);
m_tab2:=m_tab1||'_LOG';
----э
select count(*) into i from all_tables where table_name=m_tab2;
if i=0 then
m_sql:='create table'|| m_tab2 ||'
( USERID VARCHAR2(7),
COMMANDTYPE VARCHAR2(10),
DATETIME DATE,
OS_USER VARCHAR2(20) default SYS_CONTEXT(''USERENV'' ,''OS_USER''),
OS_IP VARCHAR2(15) default SYS_CONTEXT(''USERENV'',''IP_ADDRESS''),
OS_PC VARCHAR2(20) default SYS_CONTEXT(''USERENV'',''HOST''))';
execute immediate m_sql;
insert into MES_trig_log (Table_Name,Trans_Date,Errcode,Errm)
values(m_tab2,sysdate,'create TABLE',m_sql);
commit;
end if;
open cur_col(m_tab1,m_tab2);
loop
fetch cur_col into m_col;
exit when cur_col%notfound;
select t.DATA_TYPE,t.DATA_LENGTH into m_col_type,m_col_len from all_tab_columns t where t.TABLE_NAME=m_tab1 and t.COLUMN_NAME=m_col;
m_sql:='ALTER TABLE '||m_tab2 ||' ADD ' ||m_col ||' '|| m_col_type;
IF m_col_type='VARCHAR2' then
m_sql:=m_sql||'('||m_col_len||')';
elsif m_col_type='CHAR' then
m_sql:=m_sql||'('||m_col_len||')';
end if;
BEGIN
execute immediate m_sql;
insert into MES_trig_log (Table_Name,Trans_Date,Errcode,Errm)
values(m_tab2,sysdate,'alter table',m_sql);
commit;
END;
end loop;
close cur_col;
if creat_trigger=0 then---create job
m_sql:='MES_TABEL_LOG('''||m_tab1 ||''',true);';
select count(*) into i from all_jobs where what=m_sql;
if i =0 then
sys.dbms_job.submit(job => job,
what => m_sql,
next_date => sysdate+0.0007);---delay 1min run
commit;
end if;
elsif creat_trigger=1 then---эtrig
if m_tab1='EQMS' then
m_sql_filter:='update of "MACH_ID","MACH_DESC","WORK_CTR","FAC_ID","PN_CHK","FAB_INLINE","DELL_LINE","CHECK_HOLD_FLAG","WARN_MSG","HOLD_TIME","PROCESS_TYPE","R_CHECK_HOLD_FLAG","R_WARN_MSG","R_HOLD_TIME","AFTER_NUM","SHIFT_TIME","SCH_QTY" ';
else
m_sql_filter:=' update ';
end if;
m_sql:='create or replace trigger '||m_tab2||'_TRI after insert or
'||m_sql_filter||'
or delete on CPT.'||m_tab1||' for each row
declare i integer;v_userid varchar2(7);v_tab varchar2(50); v_command varchar2(10);v_sqlcode varchar2(100);v_sqlerrm varchar2(2000);
------trig create time '||to_char(sysdate,'yyyy/mm/dd hh24:mi:ss')||'
begin v_tab:='''||m_tab1||'''; -----TABEL CHANGE
begin----GET USER ID
select userid into v_userid from usersessionmap where sessionid=sys.dbms_session.unique_session_id;
exception when others then v_userid:=SYS_CONTEXT(''USERENV'',''SESSION_USERID'');
end;
SELECT COUNT(*) INTO i FROM (select COLUMN_NAME from all_tab_columns where TABLE_NAME = v_tab minus select COLUMN_NAME from all_tab_columns where TABLE_NAME = v_tab'||chr(124)||chr(124)||chr(39)||'_LOG'');
if i>0 then MES_TABEL_LOG(v_tab); end if;
if inserting then v_command:=''insert''; elsif updating then v_command:=''update''; else v_command:=''delete''; end if;
------WRITE DB
if inserting then ';
m_sql_col:='
insert into '||m_tab2||' (';
--m_sql_old:=m_sql_new;
i:=0;
open cur_col_1(m_tab1);
loop
fetch cur_col_1 into m_col;
exit when cur_col_1%notfound;
i:=i+1;
m_sql_col:=m_sql_col||m_col||',';
m_sql_value:=m_sql_value||':new.'||m_col||',';
if i=5 then
m_sql_col:=m_sql_col||'
';
m_sql_value:=m_sql_value||'
';
i:=0;
end if;
end loop;
close cur_col_1;
m_sql_col:=m_sql_col||'userid,datetime,COMMANDTYPE )
values('||m_sql_value||'v_userid,sysdate,v_command);
';
m_tem:=' else'|| replace(m_sql_col,':new.',':old.')||' end if;
exception when others then v_sqlcode:=sqlcode; v_sqlerrm:=sqlerrm;
insert into MES_trig_log (Table_Name,Trans_Date,Errcode,Errm,USERID) values(v_tab,sysdate,v_sqlcode,v_sqlerrm,v_userid);
end '||m_tab1||'_LOG_TRI;';
--execute immediate 'grant create trigger to CPT';
execute immediate m_sql||m_sql_col||m_tem;
commit;
insert into MES_trig_log (Table_Name,Trans_Date,Errcode,Errm)
values(m_tab2,sysdate,'эTRIGGER',''); --write log
commit;
end if;
EXCEPTION
when no_data_found then
null;
WHEN others THEN
v_sqlcode:=sqlcode;
v_sqlerrm:=sqlerrm;
insert into MES_trig_log (Table_Name,Trans_Date,Errcode,Errm) values(m_tab2,sysdate,v_sqlcode,v_sqlerrm);---write error log
commit;
end MES_TABEL_LOG;