-- Demonstrate trigger to protect objects in a specific schema.
-- 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 TRIGGER cs_ddl1;
DROP TABLE cs_ddl_log;
CREATE TABLE cs_ddl_log (
   ts    DATE,
   usr   VARCHAR2(32),
   evnt  VARCHAR2(20),
   stmt  LONG);

-- Trigger to fire for specific DDL statements issued by any user
-- NOTE: we must still fire for DATABASE to protect a single schema
CREATE OR REPLACE TRIGGER cs_ddl1
BEFORE CREATE OR DROP OR ALTER OR TRUNCATE ON DATABASE
DECLARE
   sql_text     ora_name_list_t;
   stmt         LONG;
   n            INTEGER;
BEGIN
   -- Trigger body will only execute for objects in SCOTT's schema
   IF (ora_dict_obj_owner = 'SCOTT') THEN

      -- 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,'Changes not allowed in SCOTT''s schema!');
      END IF;

   END IF;

END;
/