3,491
社区成员
发帖
与我相关
我的任务
分享
CREATE OR REPLACE TRIGGER TR_MEM_MEMBER_LOG
AFTER INSERT OR UPDATE OR DELETE ON MEM_MEMBER
FOR EACH ROW
DECLARE
CURSOR CUR_MEM_MEMBER_LOG_DATA IS
SELECT COLUMN_NAME, COLUMN_ID
FROM USER_TAB_COLS
WHERE TABLE_NAME = 'MEM_MEMBER'
ORDER BY COLUMN_ID;
V_COLUMN_NAME USER_TAB_COLS.COLUMN_NAME%TYPE;
V_COLUMN_ID USER_TAB_COLS.COLUMN_ID%TYPE;
V_N_COLUMN_NAME_VALUE VARCHAR2(200);
V_O_COLUMN_NAME_VALUE VARCHAR2(200);
BEGIN
--更新时写日志
IF UPDATING THEN
OPEN CUR_MEM_MEMBER_LOG_DATA;
LOOP
FETCH CUR_MEM_MEMBER_LOG_DATA
INTO V_COLUMN_NAME, V_COLUMN_ID;
EXIT WHEN CUR_MEM_MEMBER_LOG_DATA%NOTFOUND;
--有问题
--改成下面这样,但是需要注意表中的数据类型,最好在这个位置增加一个数据类型的判断
--如果是普通类型的没问题,varchar(200)可以,但是如果是日期类型的需要一个to_char函数转换
EXECUTE IMMEDIATE 'SELECT :NEW.'|| V_COLUMN_NAME||',:OLD.'||V_COLUMN_NAME||' FROM DUAL'
INTO V_N_COLUMN_NAME_VALUE,V_O_COLUMN_NAME_VALUE;
IF V_N_COLUMN_NAME_VALUE <> V_O_COLUMN_NAME_VALUE THEN
INSERT INTO MEM_MEMBER_LOG
(MEMBER_LOG_ID,
MEMBER_ID,
MENMBER_FIELDS,
MENMBER_COUNT,
MEMBER_LOG_FLAG,
OPERATION_DATE)
VALUES
(SEQ_MEM_MEMBER_LOG.NEXTVAL,
:OLD.MEMBER_ID,
V_COLUMN_NAME,
':OLD.' || V_COLUMN_NAME,
'2',
SYSDATE);
END IF;
END LOOP;
CLOSE CUR_MEM_MEMBER_LOG_DATA;
END IF;
END TR_MEM_MEMBER_LOG;
--写了个脚本生成主要部分的代码,楼主还是一个一个写吧。
DECLARE
CURSOR CUR_MEM_MEMBER_LOG_DATA IS
SELECT COLUMN_NAME, COLUMN_ID
FROM USER_TAB_COLS
WHERE TABLE_NAME = 'MEM_MEMBER'
ORDER BY COLUMN_ID;
V_COLUMN_NAME USER_TAB_COLS.COLUMN_NAME%TYPE;
V_COLUMN_ID USER_TAB_COLS.COLUMN_ID%TYPE;
V_STRSQL LONG;
BEGIN
OPEN CUR_MEM_MEMBER_LOG_DATA;
LOOP
FETCH CUR_MEM_MEMBER_LOG_DATA
INTO V_COLUMN_NAME, V_COLUMN_ID;
EXIT WHEN CUR_MEM_MEMBER_LOG_DATA%NOTFOUND;
V_STRSQL :=V_STRSQL||'
IF UPDATING('''||V_COLUMN_NAME||''') THEN '||
'INSERT INTO MEM_MEMBER_LOG '||
'(MEMBER_LOG_ID, '||
' MEMBER_ID, '||
' MENMBER_FIELDS, '||
' MENMBER_COUNT, '||
' MEMBER_LOG_FLAG, '||
' OPERATION_DATE) '||
' VALUES '||
' (SEQ_MEM_MEMBER_LOG.NEXTVAL, '||
' :OLD.MEMBER_ID, '||
''''|| V_COLUMN_NAME||''','||
' :OLD.'|| V_COLUMN_NAME||',' ||
' ''2'', '||
' SYSDATE); '||
' END IF;';
END LOOP;
CLOSE CUR_MEM_MEMBER_LOG_DATA;
--这里动态生成触发器只要部分
EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER MY_TRIGGER '||
'BEFORE UPDATE ON MEM_MEMBER '||
'FOR EACH ROW '||
'BEGIN '||
V_STRSQL ||
'END;';
END;