rem T_LOCK: DDL Trigger to protect objects not managed by PeopleTools on PeopleSoft tables
rem (c) Go-Faster Consultancy Ltd.
rem documentation: http://blog.psftdba.com/2006/04/using-ddl-triggers-to-protect-database.html
rem 17. 8.2006 initial version
rem 11.10.2006 enhancement to cater for keys defined on sub-records, omit peoplesoft alternate and key index
rem 28.10.2006 enhance for PK, MV, MV log, Partition, cluster GTT, IOT.
rem 01.10.2007 restrict FBI test to records not defined in PeopleSoft because of reintroduction of descending key indexes
rem 12.11.2007 enhancements to support key fields on subrecords
rem 10.01.2008 remove any checks on explicit DDL on triggers

set echo on feedback on verify on message on lines 100 timi on 
spool t_lock

CREATE OR REPLACE TRIGGER t_lock 
BEFORE DROP OR ALTER OR RENAME
ON SYSADM.SCHEMA
DECLARE
 e_generate_message EXCEPTION;
 l_recname     VARCHAR2(15 CHAR);
 l_table_name  VARCHAR2(30 CHAR);
 l_table_owner VARCHAR2(30 CHAR);
 l_index_owner VARCHAR2(30 CHAR);
 l_temporary   VARCHAR2(1 CHAR);
 l_partitioned VARCHAR2(3 CHAR);
 l_msg0        VARCHAR2(100 CHAR) := 'No Message.';
 l_msg         VARCHAR2(100 CHAR);
 l_msg2        VARCHAR2(100 CHAR) := 'Cannot '||ora_sysevent||' '||lower(ora_dict_obj_type)||' '||ora_dict_obj_owner||'.'||ora_dict_obj_name;
 l_errno       INTEGER := -20000; /* set a valid default in case of error in trigger*/
 l_testme      BOOLEAN := TRUE;

 sql_text ora_name_list_t;
 l_sql_stmt VARCHAR2(1000 CHAR) := ''; 
 n          INTEGER;
 i          INTEGER;

BEGIN
 l_msg := l_msg0;
 /*extract the originating SQL statement into a string variable*/
 n := ora_sql_txt(sql_text);
 FOR i IN 1..n LOOP
  l_sql_stmt := SUBSTR(l_sql_stmt || sql_text(i),1,1000);
 END LOOP;

/******************************************************************************************
* IF ora_dict_obj_type = 'TRIGGER' THEN
*
*  BEGIN --If a trigger exists, and it is not a PSU trigger
*   SELECT -20001, 'Trigger '||t.trigger_name||' exists on PeopleSoft record '||r.recname||'.'
*   INTO   l_errno, l_msg
*   FROM   all_triggers t, psrecdefn r
*   WHERE  ROWNUM = 1
*   AND    t.trigger_name = ora_dict_obj_name
*   AND    t.owner = ora_dict_obj_owner
*   AND    t.table_owner = ora_dict_obj_owner
*   AND    DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename) = t.table_name
*   AND    t.trigger_name != 'PSU'||r.recname
*   ;
*   RAISE e_generate_message;
*  EXCEPTION 
*   WHEN NO_DATA_FOUND THEN 
*    l_errno := -20000;
*    l_msg := l_msg0;
*  END;
*
* ELS
****************************************************************************************/

 IF ora_dict_obj_type = 'INDEX' THEN

  /*if referencing a index, check its on a PeopleSoft record and get the record name, table name and table owner*/
  /*qwert: no provision for rectype 7, temporary tables*/
  BEGIN 
   SELECT r.recname, i.table_owner, i.table_name,       i.owner, i.temporary, i.partitioned
   INTO   l_recname, l_table_owner, l_table_name, l_index_owner, l_temporary, l_partitioned
   FROM   psrecdefn r, all_indexes i
   WHERE  DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename) = i.table_name
   AND    i.owner = ora_dict_obj_owner
   AND    i.index_name = ora_dict_obj_name
   ;
   l_testme := TRUE;
  EXCEPTION 
   WHEN NO_DATA_FOUND THEN 
    l_recname := '';
    l_testme := FALSE;
  END;

  IF SUBSTR(ora_dict_obj_name,3,1) IN('_','0','1','2','3','4','5','6','7','8','9') AND 
     ora_dict_obj_name LIKE 'PS_'||l_recname THEN
   l_testme := FALSE;
  END IF;

  IF l_recname IS NOT NULL THEN
   l_msg := ora_dict_obj_owner||'.'||ora_dict_obj_name;
   IF l_temporary = 'Y' THEN
    l_errno := -20002;
    l_msg := l_msg||' is a global temporary index.';
    RAISE e_generate_message;
   ELSIF l_partitioned = 'YES' THEN
    l_errno := -20003;
    l_msg := l_msg||' is a partitioned index.';
    RAISE e_generate_message;
   END IF;
  END IF;

  IF l_testme THEN
   BEGIN /*raise error if a function based index exists on the table*/
    SELECT -20004, 'Function Based Index '||ora_dict_obj_name||' on table '||l_table_name||' is managed outside PeopleTools.'
    INTO   l_errno, l_msg
    FROM   DUAL
    WHERE NOT EXISTS( /*not defined in PeopleSoft*/
     SELECT 'x' /*check for indexes on record*/
     FROM   psindexdefn x
     WHERE  x.recname = l_recname
     AND    x.indexid = SUBSTR(ora_dict_obj_name,3,1)
     AND    'PS'||x.indexid||x.recname = ora_dict_obj_name
     )
    AND EXISTS( /*check for function based column*/
     SELECT 'x'
     FROM   all_ind_expressions ie
     WHERE  ie.table_owner = l_table_owner
     AND    ie.table_name = l_table_name
     AND    ie.index_owner = ora_dict_obj_owner
     AND    ie.index_name = ora_dict_obj_name)
    ;
    RAISE e_generate_message;
   EXCEPTION 
    WHEN NO_DATA_FOUND THEN 
    l_errno := -20000;
    l_msg := l_msg0;
   END; 

   BEGIN /*raise and error if an index exists on the table that is not defined in PeopleTools*/
    SELECT -20005, 'Index '||ora_dict_obj_name||' on table '||l_table_name||' is managed outside PeopleTools.'
    INTO   l_errno, l_msg
    FROM   DUAL
    WHERE NOT EXISTS( /*not defined in PeopleSoft*/
     SELECT 'x' /*check for indexes on record*/
     FROM   psindexdefn x
     WHERE  x.recname = l_recname
     AND    x.indexid = SUBSTR(ora_dict_obj_name,3,1)
     AND    'PS'||x.indexid||x.recname = ora_dict_obj_name)
    ;
    RAISE e_generate_message;
   EXCEPTION 
    WHEN NO_DATA_FOUND THEN 
    l_errno := -20000;
    l_msg := l_msg0;
   END;

  END IF;

 ELSIF ora_dict_obj_type = 'TABLE' THEN 

  /*cannot permit any alterations to PSRECDEFN because it is referenced during the trigger, causes ORA-600 [12830]*/
  IF UPPER(ora_dict_obj_name) = 'PSRECDEFN' AND ora_sysevent IN('ALTER','DROP','RENAME') THEN
   l_errno := -20006;
   l_msg := 'Cannot alter '||ora_dict_obj_name||' because trigger references it';
   RAISE e_generate_message;

  ELSIF ora_sysevent IN('DROP','RENAME') OR UPPER(l_sql_stmt) LIKE '%ALTER%TABLE%RENAME%' THEN

   /*if referencing a table, check its a PeopleSoft and get the record name, */
   /*qwert: no provision for rectype 7, temporary tables*/
   BEGIN 
    SELECT r.recname
    INTO   l_recname
    FROM   psrecdefn r
    WHERE  DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename) = ora_dict_obj_name
    ;
   EXCEPTION 
    WHEN NO_DATA_FOUND THEN 
     l_recname := '';
   END;

   IF l_recname IS NOT NULL THEN

    DECLARE 
     l_temporary    VARCHAR2(1 CHAR);
     l_partitioned  VARCHAR2(3 CHAR);
     l_cluster_name VARCHAR2(30 CHAR);
     l_iot_type     VARCHAR2(3 CHAR);
    BEGIN
     SELECT t.temporary, t.partitioned, t.cluster_name, t.iot_type
     INTO   l_temporary, l_partitioned, l_cluster_name, l_iot_type
     FROM   all_tables t
     WHERE  ROWNUM = 1
     AND    t.owner = ora_dict_obj_owner
     AND    t.table_name = ora_dict_obj_name
     ;
     l_msg := ora_dict_obj_owner||'.'||ora_dict_obj_name;
     IF l_temporary = 'Y' THEN
      l_errno := -20007;
      l_msg := l_msg||' is a global temporary table.';
      RAISE e_generate_message;
     ELSIF l_partitioned = 'YES' THEN
      l_errno := -20008;
      l_msg := l_msg||' is a partitioned table.';
      RAISE e_generate_message;
     ELSIF l_cluster_name IS NOT NULL THEN
      l_errno := -20009;
      l_msg := l_msg||' is a part of cluster '||l_cluster_name||'.';
      RAISE e_generate_message;
     ELSIF l_iot_type = 'IOT' THEN
      l_errno := -20010;
      l_msg := l_msg||' is an index organised table.';
      RAISE e_generate_message;
     END IF;
    EXCEPTION 
     WHEN NO_DATA_FOUND THEN 
      l_errno := -20000;
      l_msg := l_msg0;
    END;

    BEGIN /*raise an error if an index exists on the table that is not defined in PeopleTools*/
     SELECT -20011, 'Index '||i.index_name||' on table '||ora_dict_obj_name||' is managed outside PeopleTools.'
     INTO   l_errno, l_msg
     FROM   all_indexes i
     WHERE  ROWNUM = 1
     AND    i.table_name = ora_dict_obj_name
     AND    i.table_owner = ora_dict_obj_owner
     AND    NOT (SUBSTR(i.index_name,3,1) IN('_','0','1','2','3','4','5','6','7','8','9')
                 AND   ora_dict_obj_name LIKE 'PS_'||l_recname)
     AND    NOT EXISTS(
      SELECT 'x' /*check for indexes on record*/
      FROM   psindexdefn j
      WHERE  j.recname = l_recname
      AND    j.indexid = SUBSTR(i.index_name,3,1)
      AND    'PS'||j.indexid||j.recname = i.index_name)
     ;
     RAISE e_generate_message;
    EXCEPTION 
     WHEN NO_DATA_FOUND THEN 
      l_errno := -20000;
      l_msg := l_msg0;
    END;

    BEGIN /*raise error if a 3 based index exists on the table*/
     SELECT -20012, 'Function Based Index '||i.index_name||' on table '||i.table_name||' is managed outside PeopleTools.'
     INTO   l_errno, l_msg
     FROM   all_indexes i
     WHERE  ROWNUM = 1
     AND    i.owner = ora_dict_obj_owner
     AND    i.table_name = ora_dict_obj_name
     AND    NOT (SUBSTR(i.index_name,3,1) IN('_','0','1','2','3','4','5','6','7','8','9')
                 AND   ora_dict_obj_name LIKE 'PS_'||l_recname)
     AND NOT EXISTS( /*not defined in PeopleSoft*/
      SELECT 'x'
      FROM   psindexdefn x
      WHERE  x.recname = l_recname
      AND    x.indexid = SUBSTR(ora_dict_obj_name,3,1)
      AND    'PS'||x.indexid||x.recname = ora_dict_obj_name)
     AND EXISTS( /*check for expression column*/
      SELECT 'x'
      FROM   all_ind_expressions ie
      WHERE  ie.table_owner = i.table_owner
      AND    ie.table_name = ora_dict_obj_name
      AND    ie.index_owner = i.owner
      AND    ie.index_name = i.index_name)
     ;
     RAISE e_generate_message;
    EXCEPTION 
     WHEN NO_DATA_FOUND THEN 
      l_errno := -20000;
      l_msg := l_msg0;
    END;

    BEGIN /*if a trigger exists on the table that is not called PSU then raise an error*/
     SELECT -20013, 'Trigger '||t.trigger_name||' exists on table '||ora_dict_obj_name||'.'
     INTO   l_errno, l_msg
     FROM   all_triggers t
     WHERE  ROWNUM = 1
     AND    t.table_name = ora_dict_obj_name
     AND    t.table_owner = ora_dict_obj_owner
     AND    t.trigger_name != 'PSU'||l_recname
     ;
     RAISE e_generate_message;
    EXCEPTION
     WHEN NO_DATA_FOUND THEN 
      l_errno := -20000;
      l_msg := l_msg0;
    END;

    BEGIN /*raise and error if a primary key constraint exists on the table, and a unique index exists in PeopleSoft*/
     SELECT -20014, 'Primary key constraint '||ora_dict_obj_owner||'.'||c.constraint_name||' exists on table '||ora_dict_obj_name||'.'
     INTO   l_errno, l_msg
     FROM   all_constraints c
     WHERE  ROWNUM = 1
     AND    c.table_name = ora_dict_obj_name
     AND    c.owner = ora_dict_obj_owner
     AND    c.constraint_type = 'P' /*primary key constraint*/
     AND    c.index_name = 'PS_'||l_recname
     AND    EXISTS(SELECT 'x' /*a unique key field*/
      FROM  psrecfielddb r, psrecfield f
      WHERE r.recname = l_recname
      AND   f.recname = r.recname_parent
      AND   f.fieldname = r.fieldname
      AND   MOD(f.useedit,2) = 1)
     AND NOT EXISTS(SELECT 'x' /*a duplicate key field is not defined*/
      FROM  psrecfielddb r, psrecfield f
      WHERE r.recname = l_recname
      AND   f.recname = r.recname_parent
      AND   f.fieldname = r.fieldname
      AND   MOD((f.useedit/2),2) = 1)
     ;
     RAISE e_generate_message;
    EXCEPTION 
     WHEN NO_DATA_FOUND THEN 
      l_errno := -20000;
      l_msg := l_msg0;
    END;

    BEGIN /*if a materialized view log exists*/
     SELECT -20015, 'A materialized view log exists on table '||ora_dict_obj_name||'.'
     INTO   l_errno, l_msg
     FROM   all_mview_logs l
     WHERE  ROWNUM = 1
     AND    l.master = ora_dict_obj_name
     AND    l.log_owner = ora_dict_obj_owner
     ;
     RAISE e_generate_message;
    EXCEPTION
     WHEN NO_DATA_FOUND THEN 
      l_errno := -20000;
      l_msg := l_msg0;
    END;

    BEGIN /*if a materialized view exists*/
     SELECT -20016, 'Table '||ora_dict_obj_name||' is a materialized view.'
     INTO   l_errno, l_msg
     FROM   all_mviews m
     WHERE  ROWNUM = 1
     AND    m.mview_name = ora_dict_obj_name
     AND    m.owner = ora_dict_obj_owner
     ;
     RAISE e_generate_message;
    EXCEPTION
     WHEN NO_DATA_FOUND THEN 
      l_errno := -20000;
      l_msg := l_msg0;
    END;

   END IF;
  END IF;
 END IF;

EXCEPTION
 WHEN NO_DATA_FOUND THEN 
  l_errno := -20000;
  l_msg := l_msg0;
 WHEN e_generate_message THEN
  RAISE_APPLICATION_ERROR(l_errno,'T_LOCK: '||l_msg||CHR(10)||l_msg2||CHR(10)||'SQL:'||l_sql_stmt);
END;
/

show errors
spool off