CREATE OR REPLACE TRIGGER tr_trace_ddl
AFTER DDL ON DATABASE
DECLARE
sql_text ora_name_list_t;
state_sql ddl$trace.ddl_sql%TYPE;
BEGIN
FOR i IN 1..ora_sql_txt(sql_text) LOOP
state_sql := state_sql||sql_text(i);
END LOOP;
INSERT INTO ddl$trace(login_user,ddl_time,ip_address,audsid,
schema_user,schema_object,ddl_sql)
VALUES(ora_login_user,SYSDATE,userenv('SESSIONID'),
sys_context('USERENV','IP_ADDRESS'),
ora_dict_obj_owner,ora_dict_obj_name,state_sql);
EXCEPTION
WHEN OTHERS THEN
sp_write_log('Capture DDL Excption:'||SQLERRM);
END tr_trace_ddl;
在创建以上触发器时要注意几点
1、必须创建一个ddl$trace的表,用来记录ddl的记录
2、sp_write_log原本是一个写日志的过程,可以置换为自己的需要,如null跳过。
AFTER DDL ON DATABASE
DECLARE
sql_text ora_name_list_t;
state_sql ddl$trace.ddl_sql%TYPE;
BEGIN
FOR i IN 1..ora_sql_txt(sql_text) LOOP
state_sql := state_sql||sql_text(i);
END LOOP;
INSERT INTO ddl$trace(login_user,ddl_time,ip_address,audsid,
schema_user,schema_object,ddl_sql)
VALUES(ora_login_user,SYSDATE,userenv('SESSIONID'),
sys_context('USERENV','IP_ADDRESS'),
ora_dict_obj_owner,ora_dict_obj_name,state_sql);
EXCEPTION
WHEN OTHERS THEN
sp_write_log('Capture DDL Excption:'||SQLERRM);
END tr_trace_ddl;
在创建以上触发器时要注意几点
1、必须创建一个ddl$trace的表,用来记录ddl的记录
2、sp_write_log原本是一个写日志的过程,可以置换为自己的需要,如null跳过。
在触发器中相应关键处(如要删除表记录)用dbms_output.putline('delete !!!');
等语句进行标示!在sqlplus下先执行set serverout on 然后就可以执行出发触发器的
条件了!看看print的内容是不是你想要的流程和效果!如果有错误提示还可以相应的
进行检查!
另外:
1、最好别对PLSQL Developer和toad产生依赖!有的环境下没有toad等单步调试工具,你该怎么办!?!
2、在测试之前,将可能产生影响的表,做一下备份:
create table tablename_bak as select * from tablename;
3、祝你能顺利回家过年!一路顺风!
祝大家新春快乐,万事如意
直接用dbms_output.put_line(你想要的信息);
---------------------------------------
DROP TRIGGER CityAUDIT;CREATE TRIGGER CityAUDIT
AFTER INSERT OR UPDATE OR DELETE
ON City
FOR EACH ROWDECLARE
RASEQ CityAUDIT.ASEQ%TYPE;
BEGIN
IF INSERTING THEN
SELECT NVL(MAX(ASEQ),0) + 1
INTO RASEQ
FROM CityAUDIT
WHERE CityCode = :NEW.CityCode; INSERT INTO CityAUDIT
(CityPhoneCode,
CityCode,
CityName,
CountryCode,
GMTDiff,
ASEQ,
ASTA,
INPUTER,
INPUTDATE)
VALUES
(:NEW.CityPhoneCode,
:NEW.CityCode,
:NEW.CityName,
:NEW.CountryCode,
:NEW.GMTDiff,
RASEQ,
'1',
USER,
SYSDATE);
END IF; IF UPDATING THEN
SELECT ASEQ + 1
INTO RASEQ
FROM CityAUDIT
WHERE CityCode = :NEW.CityCode
AND ASTA = '1'; UPDATE CityAUDIT
SET ASTA = '3'
WHERE CityCode = :NEW.CityCode
AND ASTA = '1'; INSERT INTO CityAUDIT
(CityPhoneCode,
CityCode,
CityName,
CountryCode,
GMTDiff,
ASEQ,
ASTA,
INPUTER,
INPUTDATE)
VALUES
(:NEW.CityPhoneCode,
:NEW.CityCode,
:NEW.CityName,
:NEW.CountryCode,
:NEW.GMTDiff,
RASEQ,
'1',
USER,
SYSDATE);
END IF;
IF DELETING THEN
SELECT ASEQ + 1
INTO RASEQ
FROM CityAUDIT
WHERE CityCode = :OLD.CityCode
AND ASTA = '1'; UPDATE CityAUDIT
SET ASTA = '3'
WHERE CityCode = :OLD.CityCode
AND ASTA = '1'; INSERT INTO CityAUDIT
(CityPhoneCode,
CityCode,
CityName,
CountryCode,
GMTDiff,
ASEQ,
ASTA,
INPUTER,
INPUTDATE)
VALUES
(:OLD.CityPhoneCode,
:OLD.CityCode,
:OLD.CityName,
:OLD.CountryCode,
:OLD.GMTDiff,
RASEQ,
'2',
USER,
SYSDATE);
END IF;END;
/
--------------------------------------------------------
以上依自己情况可以简化的。
如测试语句,在sqlplus下可以测(前提要有trigger表),或在toad、PLSQL Developer都可以的。
如测试功能,在招待删除表记录后,查看trigger表的数据,就实现了。