有个自动化工具可以给表加上触发器, 代码在最后;
使用方法如下:
1. 首先要改 TAB_NME, 这个要改成想要加trigger 的table name, 比如: RCV_TRANSACTIONS_INTERFACE
2. 其次要改 TRIG_NME, 这个要改成trigger 的名字, 比如: YU_RTI_INSERT_DELETE
3. 放在SQL Developer 里面跑这段代码, 会输出新的一段代码, 这段代码就是用于生成trigger 的代码;
4. 拷贝输出的代码, 重新跑一次, 于是在输出窗口就会显示编译已成功: TRIGGER YU_RTI_INSERT_DELETE 已编译
注意: 输出的代码第一行会有一句话: 匿名块已完成. 这个要删掉...
6. 重现bug 之后收集的log 里面就会有这个trigger: YU_RTI_INSERT_DELETE: ...........INSERTING.......... 等等, 看上下文就可以很快找到插入的代码
set serveroutput on size 999999 ; DECLARE TAB_NME VARCHAR2(100) := UPPER('RCV_TRANSACTIONS_INTERFACE'); TRIG_NME VARCHAR2(100) := UPPER('YU_RTI_INSERT_DELETE'); BEGIN DBMS_OUTPUT.PUT_LINE('CREATE OR REPLACE TRIGGER ' ||TRIG_NME); DBMS_OUTPUT.PUT_LINE('before insert or delete'); DBMS_OUTPUT.PUT_LINE('on ' ||TAB_NME); DBMS_OUTPUT.PUT_LINE('for each row '); DBMS_OUTPUT.PUT_LINE('declare '); DBMS_OUTPUT.PUT_LINE('pragma AUTONOMOUS_TRANSACTION; '); DBMS_OUTPUT.PUT_LINE('begin '); DBMS_OUTPUT.PUT_LINE('IF INSERTING THEN '); DBMS_OUTPUT.PUT_LINE(' '); DBMS_OUTPUT.PUT_LINE(' inv_trx_util_pub.trace('' ...........INSERTING..........'', ''' ||TRIG_NME ||''', 1);'); DBMS_OUTPUT.PUT_LINE(' '); FOR I IN (SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE TABLE_NAME LIKE UPPER(TAB_NME) GROUP BY COLUMN_NAME, COLUMN_ID ORDER BY COLUMN_ID) LOOP DBMS_OUTPUT.PUT_LINE('if :NEW.' ||I.COLUMN_NAME ||' is not null then '); DBMS_OUTPUT.PUT_LINE(' inv_trx_util_pub.trace('' new.' ||I.COLUMN_NAME ||' : '' || :NEW.' ||I.COLUMN_NAME ||', ''' ||TRIG_NME ||''', 1);'); DBMS_OUTPUT.PUT_LINE('end if;'); END LOOP; DBMS_OUTPUT.PUT_LINE(' '); DBMS_OUTPUT.PUT_LINE('ELSIF DELETING THEN '); DBMS_OUTPUT.PUT_LINE(' '); DBMS_OUTPUT.PUT_LINE(' inv_trx_util_pub.trace('' ...........DELETING..........'', ''' ||TRIG_NME ||''', 1);'); DBMS_OUTPUT.PUT_LINE(' '); FOR I IN (SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE TABLE_NAME LIKE UPPER(TAB_NME) GROUP BY COLUMN_NAME, COLUMN_ID ORDER BY COLUMN_ID) LOOP DBMS_OUTPUT.PUT_LINE('if :OLD.' ||I.COLUMN_NAME ||' is not null then '); DBMS_OUTPUT.PUT_LINE(' inv_trx_util_pub.trace('' old.' ||I.COLUMN_NAME ||' : '' || :OLD.' ||I.COLUMN_NAME ||', ''' ||TRIG_NME ||''', 1);'); DBMS_OUTPUT.PUT_LINE('end if;'); END LOOP; DBMS_OUTPUT.PUT_LINE(' '); DBMS_OUTPUT.PUT_LINE('END IF;'); DBMS_OUTPUT.PUT_LINE('END '||TRIG_NME||';'); END;
set serveroutput on size 100000 ; DECLARE TAB_NME VARCHAR2(100) := UPPER('table_name'); TRIG_NME VARCHAR2(100) := UPPER('yu_mmt_update'); BEGIN DBMS_OUTPUT.PUT_LINE('CREATE OR REPLACE TRIGGER ' ||TRIG_NME); DBMS_OUTPUT.PUT_LINE('before update '); DBMS_OUTPUT.PUT_LINE('on ' ||TAB_NME); DBMS_OUTPUT.PUT_LINE('for each row '); DBMS_OUTPUT.PUT_LINE('declare '); DBMS_OUTPUT.PUT_LINE('pragma AUTONOMOUS_TRANSACTION; '); DBMS_OUTPUT.PUT_LINE('begin '); DBMS_OUTPUT.PUT_LINE(' '); DBMS_OUTPUT.PUT_LINE('IF UPDATING THEN '); DBMS_OUTPUT.PUT_LINE(' '); DBMS_OUTPUT.PUT_LINE(' inv_trx_util_pub.trace('' ...........UPDATING..........'', ''' ||TRIG_NME ||''', 1);'); DBMS_OUTPUT.PUT_LINE(' '); FOR I IN ( SELECT COLUMN_NAME, COLUMN_TYPE FROM ( SELECT T.COLUMN_NAME, NVL(C.COLUMN_TYPE,'N') COLUMN_TYPE, T.COLUMN_ID FROM ALL_TAB_COLUMNS T, (SELECT AIC.COLUMN_NAME, 'U' COLUMN_TYPE FROM ALL_INDEXES AI, ALL_IND_COLUMNS AIC WHERE AI.TABLE_NAME LIKE UPPER(TAB_NME) AND AI.UNIQUENESS = 'UNIQUE' AND AI.INDEX_NAME = AIC.INDEX_NAME UNION SELECT B.COLUMN_NAME, 'P' COLUMN_TYPE FROM ALL_CONSTRAINTS A, ALL_CONS_COLUMNS B WHERE A.CONSTRAINT_TYPE = 'P' AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND A.TABLE_NAME LIKE UPPER(TAB_NME)) C WHERE T.TABLE_NAME LIKE UPPER(TAB_NME) AND T.COLUMN_NAME = C.COLUMN_NAME (+) ) GROUP BY COLUMN_NAME, COLUMN_TYPE, COLUMN_ID ORDER BY COLUMN_ID) LOOP DBMS_OUTPUT.PUT_LINE('if :OLD.' ||I.COLUMN_NAME ||' != :NEW.' ||I.COLUMN_NAME); DBMS_OUTPUT.PUT_LINE(' OR (:OLD.' ||I.COLUMN_NAME ||' is null and :NEW.' ||I.COLUMN_NAME ||' is not null) OR (:OLD.' ||I.COLUMN_NAME ||' is not null and :NEW.' ||I.COLUMN_NAME ||' is null) '); DBMS_OUTPUT.PUT_LINE(' OR ''' ||I.COLUMN_TYPE ||'''=''U'' OR ''' ||I.COLUMN_TYPE ||'''=''P'' then '); DBMS_OUTPUT.PUT_LINE(' inv_trx_util_pub.trace('' old.' ||I.COLUMN_NAME ||' : '' || :OLD.' ||I.COLUMN_NAME ||'|| '', new.' ||I.COLUMN_NAME ||' : '' || :NEW.' ||I.COLUMN_NAME ||', ''' ||TRIG_NME ||''', 1);'); DBMS_OUTPUT.PUT_LINE('end if;'); END LOOP; DBMS_OUTPUT.PUT_LINE(' '); DBMS_OUTPUT.PUT_LINE('END IF;'); DBMS_OUTPUT.PUT_LINE('END '||TRIG_NME||';'); END; --select * from user_triggers where trigger_name like 'YU%';