oracle ddl 触发器

建立一个日志表

CREATE TABLE ddl_log (
operation   VARCHAR2(30),
obj_owner   VARCHAR2(30),
object_name VARCHAR2(30),
sql_text    VARCHAR2(64),
attempt_by  VARCHAR2(30),
attempt_dt  DATE);

在truncate 操作前触发，写日志到ddl_log表CREATE OR REPLACE TRIGGER bf_truncate

BEFORE  TRUNCATE
ON SCHEMA

DECLARE
oper ddl_log.operation%TYPE; 
BEGIN

INSERT INTO ddl_log
SELECT ora_sysevent, ora_dict_obj_owner, 
  ora_dict_obj_name, null, USER, SYSDATE
FROM DUAL;
COMMIT;

END bf_truncate;

禁止drop 或者truncate 操作

CREATE OR REPLACE PROCEDURE log_proc (
ose  ddl_log.operation%TYPE,
odoo ddl_log.obj_owner%TYPE,
odon ddl_log.object_name%TYPE)
IS

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN
INSERT INTO ddl_log
SELECT ora_sysevent, ora_dict_obj_owner, 
  ora_dict_obj_name, 'Not Allowed', USER, SYSDATE
FROM DUAL;
COMMIT;
END log_proc;



CREATE OR REPLACE TRIGGER save_our_db
BEFORE DROP OR TRUNCATE
ON SCHEMA

DECLARE
oper ddl_log.operation%TYPE; 
BEGIN
SELECT ora_sysevent
INTO oper
FROM DUAL;

  log_proc(ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name);

IF oper = 'DROP' THEN
    RAISE_APPLICATION_ERROR(-20998, 'Attempt To Drop
    In Production Has Been Logged');
  ELSIF oper = 'TRUNCATE' THEN
    RAISE_APPLICATION_ERROR(-20999, 'Attempt To Truncate A 
    Production Table Has Been Logged');
END IF;
END save_our_db;

更多可以捕捉的DLL

BEFORE / AFTER ALTER
BEFORE / AFTER ANALYZE
BEFORE / AFTER ASSOCIATE STATISTICS
BEFORE / AFTER AUDIT
BEFORE / AFTER COMMENT
BEFORE / AFTER CREATE
BEFORE / AFTER DDL
BEFORE / AFTER DISASSOCIATE STATISTICS
BEFORE / AFTER DROP
BEFORE / AFTER GRANT
BEFORE / AFTER NOAUDIT
BEFORE / AFTER RENAME
BEFORE / AFTER REVOKE
BEFORE / AFTER TRUNCATE
AFTER SUSPEND

了解更多...

转载于:https://www.cnblogs.com/rambler/archive/2012/03/14/2395508.html