DDL Trigger

- create, alter, drop 행위를 추적하는 Trigger를 만듬

 

CREATE OR REPLACE TRIGGER audit_object_create AFTER CREATE ON SCHEMA -- 현재 내 SCHEMA 안에서만 추적하라 ( 전체라면 DATABASE로 )
BEGIN
 INSERT INTO trigger_log
 VALUES ('CREATE ' || sys.dictionary_obj_type || ' ' || sys.dictionary_obj_name || ' by ' || sys.login_user, sysdate);
END;

 

 

CREATE OR REPLACE TRIGGER audit_object_alter AFTER ALTER ON SCHEMA
BEGIN
 INSERT INTO trigger_log
 VALUES ('ALTER ' || sys.dictionary_obj_type || ' ' || sys.dictionary_obj_name || ' by ' || sys.login_user, sysdate);
END;

 

 

CREATE OR REPLACE TRIGGER audit_object_drop AFTER DROP ON SCHEMA
BEGIN
 INSERT INTO trigger_log
 VALUES ('DROP ' || sys.dictionary_obj_type || ' ' || sys.dictionary_obj_name || ' by ' || sys.login_user, sysdate);
END;

 

 

 

 

 

 

 

 

CREATE TABLE test(a char);

 

select * from trigger_log;
 

 

 

ALTER TABLE test MODIFY a char(2);

 

select log, to_char(timestamp, 'YYYY-MM-DD HH24:MI:SS')
FROM trigger_log ORDER BY timestamp DESC;
 

 

drop table test;

 

select log, to_char(timestamp, 'YYYY-MM-DD HH24:MI:SS')
FROM trigger_log ORDER BY timestamp DESC;