-- Demonstrate trigger that fires for any DDL statement in the database.
-- Allow only a certain user to make DDL changes, and log each change so it
-- can be included in the original data model.
-- Copyright (c) 2004, Caleb.com

-- Create the log table
DROP TABLE cs_ddl_log;
CREATE TABLE cs_ddl_log (
   ts    DATE,
   usr   VARCHAR2(32),
   evnt  VARCHAR2(20),
   stmt  LONG);

-- Trigger to fire for any DDL statement issued by any user
-- NOTE: if you change 'DATABASE' to a specific schema (eg. 'hr.SCHEMA')
-- the trigger will fire for user HR modifying any object in the database.
-- Other users will still be able to modify objects in schema HR!
CREATE OR REPLACE TRIGGER cs_ddl1
BEFORE DDL ON DATABASE
DECLARE
   sql_text     ora_name_list_t;
   stmt         LONG;
   n            INTEGER;
BEGIN
   -- Only the privileged user is allowed to proceed
   IF (ora_login_user = 'CALEB') THEN
      -- Extract the original DDL statement and piece it together into a LONG
      n := ora_sql_txt( sql_text );
      FOR i IN 1..n LOOP
         stmt := stmt || sql_text(i);
      END LOOP;
      -- Insert into log table.  Note, if original DDL statement fails, this
      -- insert will also be rolled back (statement level rollback), so this
      -- only logs successful DDL statements.
      INSERT INTO cs_ddl_log
         VALUES ( sysdate, ora_login_user, ora_sysevent, stmt);
   
   -- All other users get the rasberry!
   ELSE
      raise_application_error(-20001,'DDL not allowed in production database!');
   END IF;
END;
/