DDL Triggers

CREATE OR REPLACE TRIGGER <trigger_name>
<BEFORE | AFTER> <triggering_action>
ON <SCHEMA | DATABASE>

DECLARE
 -- variable declarations
BEGIN
  -- trigger code
EXCEPTION
  -- exception handler
END <trigger_name>;
/





CREATE OR REPLACE TRIGGER bcs_trigger
BEFORE CREATE
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;
END bcs_trigger;
/

col object_name format a20

SELECT object_name, object_type
FROM user_objects;

col triggering_event format a20
SELECT trigger_name, trigger_type, 
triggering_event, base_object_type
FROM user_triggers;

SELECT * FROM ddl_log;

CREATE SEQUENCE s1_test;

CREATE TABLE t1_test (
testcol VARCHAR2(20));

CREATE OR REPLACE VIEW v_test AS
SELECT * FROM t_test;

set linesize 150

SELECT operation, obj_owner, object_name
FROM ddl_log;

TRUNCATE TABLE ddl_log;

conn system/manager

CREATE TABLE uwclass.xyz (
testcol VARCHAR2(20));

conn uwclass/uwclass

SELECT operation, obj_owner, object_name
FROM ddl_log;


TRUNCATE TABLE ddl_log;



















CREATE OR REPLACE TRIGGER <trigger_name>
<BEFORE | AFTER> <triggering_action>
ON <SCHEMA | DATABASE>

DECLARE
 -- variable declarations
BEGIN
  -- trigger code
EXCEPTION
  -- exception handler
END <trigger_name>;
/




conn system/manager

GRANT administer database trigger TO uwclass;

conn uwclass/uwclass

CREATE OR REPLACE TRIGGER bcd_trigger
BEFORE CREATE
ON DATABASE

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;
END bcd_trigger;
/

col object_name format a20
SELECT object_name, object_type
FROM user_objects;

col triggering_event format a20
SELECT trigger_name, trigger_type, 
triggering_event, base_object_type
FROM user_triggers;

SELECT * FROM ddl_log;

CREATE SEQUENCE s2_test;

CREATE TABLE t2_test (
testcol VARCHAR2(20));

CREATE OR REPLACE VIEW v_test AS
SELECT * FROM t_test;

set linesize 150

SELECT operation, obj_owner, object_name
FROM ddl_log;

TRUNCATE TABLE ddl_log;

conn system/manager

CREATE TABLE uwclass.xyz (
testcol VARCHAR2(20));

conn uwclass/uwclass

SELECT operation, obj_owner, object_name
FROM ddl_log;

DROP TRIGGER bcd_trigger;


TRUNCATE TABLE ddl_log;