create or replace trigger app_type_mjl_tr
after insert or update or delete on app_type_mjl
for each row
declare
-- local variables here COL_NAME varchar2(50);
V_B_STRSQL varchar2(1900);
V_A_STRSQL varchar2(1900);
V_B_VALUE VARCHAR2(1900);
V_A_VALUE VARCHAR2(1900);
for j in (SELECT col.COLUMN_NAME col
FROM ALL_COL_COMMENTS col
WHERE TABLE_NAME = 'APP_TYPE_MJL') loop
COL_NAME := j.col;
if updating(COL_NAME) then
/* V_STRSQL := 'INSERT INTO app_wz_logs ' || '(ID, ' || ' LOG_TYPE, ' ||
' DATA_ID, ' || ' DATA_NO, ' || ' OPERAT_NOTE, ' ||
' OPERAT_BEFORE_VALUE, ' || ' OPERAT_AFTER_VALUE, ' ||
' OPERAT_USER, ' || ' OPERAT_DATE) ' || ' VALUES ' ||
' (app_wz_logs_s.NEXTVAL, ' || ' ''TYPE'',' || ':NEW.ID,' ||
':NEW.TYPE_NAME,' || '''修改一条类别记录:''|| COL_NAME,' ||
':OLD.' || COL_NAME || ',' || ':NEW.' || COL_NAME || ',' ||
':NEW.CREATE_BY,' || 'SYSDATE)';
*/
V_B_STRSQL := 'SELECT :OLD.'|| COL_NAME || ' FROM DUAL ';
V_A_STRSQL := 'SELECT :NEW.'|| COL_NAME || ' FROM DUAL ';
DBMS_OUTPUT.PUT_LINE(V_B_STRSQL);
DBMS_OUTPUT.PUT_LINE(V_A_STRSQL);
/*
execute immediate V_B_STRSQL into V_B_VALUE;
execute immediate V_A_STRSQL into V_A_VALUE;
INSERT INTO app_wz_logs
(ID,
LOG_TYPE,
DATA_ID,
DATA_NO,
OPERAT_NOTE,
OPERAT_BEFORE_VALUE,
OPERAT_AFTER_VALUE,
OPERAT_USER,
OPERAT_DATE)
VALUES
(app_wz_logs_s.NEXTVAL,
'TYPE',
:NEW.ID,
:NEW.TYPE_NAME,
'修改一条类别记录:' || COL_NAME,
V_B_VALUE,
V_A_VALUE,
:NEW.CREATE_BY,
SYSDATE);*/
end if;
end loop;end;
after insert or update or delete on app_type_mjl
for each row
declare
-- local variables here COL_NAME varchar2(50);
V_B_STRSQL varchar2(1900);
V_A_STRSQL varchar2(1900);
V_B_VALUE VARCHAR2(1900);
V_A_VALUE VARCHAR2(1900);
for j in (SELECT col.COLUMN_NAME col
FROM ALL_COL_COMMENTS col
WHERE TABLE_NAME = 'APP_TYPE_MJL') loop
COL_NAME := j.col;
if updating(COL_NAME) then
/* V_STRSQL := 'INSERT INTO app_wz_logs ' || '(ID, ' || ' LOG_TYPE, ' ||
' DATA_ID, ' || ' DATA_NO, ' || ' OPERAT_NOTE, ' ||
' OPERAT_BEFORE_VALUE, ' || ' OPERAT_AFTER_VALUE, ' ||
' OPERAT_USER, ' || ' OPERAT_DATE) ' || ' VALUES ' ||
' (app_wz_logs_s.NEXTVAL, ' || ' ''TYPE'',' || ':NEW.ID,' ||
':NEW.TYPE_NAME,' || '''修改一条类别记录:''|| COL_NAME,' ||
':OLD.' || COL_NAME || ',' || ':NEW.' || COL_NAME || ',' ||
':NEW.CREATE_BY,' || 'SYSDATE)';
*/
V_B_STRSQL := 'SELECT :OLD.'|| COL_NAME || ' FROM DUAL ';
V_A_STRSQL := 'SELECT :NEW.'|| COL_NAME || ' FROM DUAL ';
DBMS_OUTPUT.PUT_LINE(V_B_STRSQL);
DBMS_OUTPUT.PUT_LINE(V_A_STRSQL);
/*
execute immediate V_B_STRSQL into V_B_VALUE;
execute immediate V_A_STRSQL into V_A_VALUE;
INSERT INTO app_wz_logs
(ID,
LOG_TYPE,
DATA_ID,
DATA_NO,
OPERAT_NOTE,
OPERAT_BEFORE_VALUE,
OPERAT_AFTER_VALUE,
OPERAT_USER,
OPERAT_DATE)
VALUES
(app_wz_logs_s.NEXTVAL,
'TYPE',
:NEW.ID,
:NEW.TYPE_NAME,
'修改一条类别记录:' || COL_NAME,
V_B_VALUE,
V_A_VALUE,
:NEW.CREATE_BY,
SYSDATE);*/
end if;
end loop;end;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货