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

了解更多...