rem T_LOCK: DDL Trigger to protect objects not managed by PeopleTools on PeopleSoft tables
rem (c) Go-Faster Consultancy Ltd.
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.

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_msg      VARCHAR2(100 CHAR) := 'No Message.';
 l_msg2     VARCHAR2(100 CHAR) := 'Cannot '||ora_sysevent||' '||lower(ora_dict_obj_type)||' '||ora_dict_obj_owner||'.'||ora_dict_obj_name;

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

BEGIN
 /*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 'Trigger '||t.trigger_name||' exists on PeopleSoft record '||r.recname||'.'
   INTO   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 
    NULL;
  END;

 ELSIF ora_dict_obj_type = 'INDEX' THEN

  BEGIN /*raise error if a function based index exists on the table*/
   SELECT 'Function Based Index '||i.index_name||' on table '||i.table_name||' is managed outside PeopleTools.'
   INTO   l_msg
   FROM   psrecdefn r, all_indexes i
   WHERE  ROWNUM = 1
   AND    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
   AND EXISTS( /*check for index column not on table*/
    SELECT 'x'
    FROM   all_ind_columns ic
    WHERE  ic.table_owner = i.table_owner
    AND	   ic.table_name = i.table_name
    AND    ic.index_owner = ora_dict_obj_owner
    AND	   ic.index_name = ora_dict_obj_name
    AND NOT EXISTS(
     SELECT 'x'

     FROM   all_tab_columns tc

     WHERE  tc.owner = i.table_owner

     AND    tc.table_name = i.table_name

     AND    tc.column_name = ic.column_name))
   ;
   RAISE e_generate_message;
  EXCEPTION 
   WHEN NO_DATA_FOUND THEN 
    NULL;
  END;

  BEGIN /*raise and error if an index exists on the table that is not defined in PeopleTools*/
   SELECT 'Index '||i.index_name||' on table '||i.table_name||' is managed outside PeopleTools.'
   INTO l_msg
   FROM psrecdefn r, all_indexes i
   WHERE ROWNUM = 1
   AND DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename) = i.table_name
   AND i.index_name = ora_dict_obj_name
   AND i.owner = ora_dict_obj_owner
   AND NOT EXISTS(
    SELECT 'x' /*check for indexes on record*/

    FROM   psindexdefn j

    WHERE  j.recname = r.recname

    AND	   j.indexid = SUBSTR(i.index_name,3,1)

    AND    'PS'||j.indexid||r.recname = i.index_name

    UNION ALL

    SELECT 'x' /*check for alter key indexes on subrecords*/

    FROM   DUAL

    WHERE  SUBSTR(i.index_name,3,1) IN('_','0','1','2','3','4','5','6','7','8','9')

    AND	   i.index_name = 'PS'||SUBSTR(i.index_name,3,1)||r.recname)
   ;
   RAISE e_generate_message;
  EXCEPTION 
   WHEN NO_DATA_FOUND THEN 
    NULL;
  END;

  DECLARE 
   l_temporary   VARCHAR2(1 CHAR);
   l_partitioned VARCHAR2(3 CHAR);
  BEGIN
   SELECT i.temporary, i.partitioned
   INTO   l_temporary, l_partitioned
   FROM   psrecdefn r, all_indexes i
   WHERE  ROWNUM = 1
   AND    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_msg := ora_dict_obj_owner||'.'||ora_dict_obj_name;
   IF l_temporary = 'Y' THEN
    l_msg := l_msg||' is a global temporary index.';
    RAISE e_generate_message;
   ELSIF l_partitioned = 'YES' THEN
    l_msg := l_msg||' is a partitioned index.';
    RAISE e_generate_message;
   END IF;
  EXCEPTION 
   WHEN NO_DATA_FOUND THEN 
    NULL;
  END;

 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 UPPER(l_sql_stmt) LIKE '%ALTER%' THEN
   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 dropping a table, first get the PeopleSoft record name*/
   SELECT r.recname
   INTO   l_recname
   FROM   psrecdefn r
   WHERE  DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename) = ora_dict_obj_name
   ;

   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   psrecdefn r, all_tables t
    WHERE  ROWNUM = 1
    AND    DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename) = t.table_name
    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_msg := l_msg||' is a global temporary table.';
     RAISE e_generate_message;
    ELSIF l_partitioned = 'YES' THEN
     l_msg := l_msg||' is a partitioned table.';
     RAISE e_generate_message;
    ELSIF l_cluster_name IS NOT NULL THEN
     l_msg := l_msg||' is a part of cluster '||l_cluster_name||'.';
     RAISE e_generate_message;
    ELSIF l_iot_type = 'IOT' THEN
     l_msg := l_msg||' is an index organised table.';
     RAISE e_generate_message;
    END IF;
   EXCEPTION 
    WHEN NO_DATA_FOUND THEN 
     NULL;
   END;

   BEGIN /*raise error if a function based index exists on the table*/
    SELECT 'Function Based Index '||i.index_name||' on table '||i.table_name||' is managed outside PeopleTools.'
    INTO l_msg
    FROM psrecdefn r, all_indexes i
    WHERE ROWNUM = 1
    AND DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename) = ora_dict_obj_name
    AND i.owner = ora_dict_obj_owner
    AND i.table_name = ora_dict_obj_name
    AND EXISTS(
     SELECT 'x'
     FROM   all_ind_columns ic
     WHERE  ic.table_owner = i.table_owner
     AND    ic.table_name = ora_dict_obj_name
     AND    ic.index_owner = i.owner
     AND    ic.index_name = i.index_name     
     AND NOT EXISTS(
      SELECT 'x'
      FROM   all_tab_columns tc

      WHERE  tc.owner = i.table_owner

      AND    tc.table_name = ora_dict_obj_name
      AND    tc.column_name = ic.column_name)
)
    ;
    RAISE e_generate_message;
   EXCEPTION 
    WHEN NO_DATA_FOUND THEN 
     NULL;
   END;

   BEGIN /*raise and error if an index exists on the table that is not defined in PeopleTools*/
    SELECT 'Index '||i.index_name||' on table '||ora_dict_obj_name||' is managed outside PeopleTools.'
    INTO   l_msg
    FROM   psrecdefn r, 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) BETWEEN '0' AND '9'   
    AND    DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename) = i.table_name
    AND    NOT EXISTS(
     SELECT 'x' /*check for indexes on record*/

     FROM   psindexdefn j

     WHERE  j.recname = r.recname
     AND    j.indexid = SUBSTR(i.index_name,3,1)
     AND    'PS'||j.indexid||r.recname = i.index_name
     UNION ALL
     SELECT 'x' /*check for alternate key indexes on subrecords*/
     FROM   DUAL
     WHERE  SUBSTR(i.index_name,3,1) IN('_','0','1','2','3','4','5','6','7','8','9')
     AND    i.index_name = 'PS'||SUBSTR(i.index_name,3,1)||r.recname)
    ;
    RAISE e_generate_message;
   EXCEPTION 
    WHEN NO_DATA_FOUND THEN 
     NULL;
   END;

   BEGIN /*if a trigger exists on the table that is not called PSU then raise an error*/
    SELECT 'Trigger '||t.trigger_name||' exists on table '||ora_dict_obj_name||'.'
    INTO   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 
     NULL;
   END;

   BEGIN /*raise and error if a primary key constraint exists on the table, and a unique index exists in PeopleSoft*/
    SELECT 'Primary key constraint '||ora_dict_obj_owner||'.'||c.constraint_name||' exists on table '||ora_dict_obj_name||'.'
    INTO   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 
     NULL;
   END;

   BEGIN /*if a materialized view log exists*/
    SELECT 'A materialized view log exists on table '||ora_dict_obj_name||'.'
    INTO   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 
     NULL;
   END;

   BEGIN /*if a materialized view exists*/
    SELECT 'Table '||ora_dict_obj_name||' is a materialized view.'
    INTO   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 
     NULL;
   END;

  END IF;
 END IF;

EXCEPTION
 WHEN NO_DATA_FOUND THEN 
  NULL;
 WHEN e_generate_message THEN
  RAISE_APPLICATION_ERROR(-20042,'T_LOCK: '||l_msg||CHR(10)||l_msg2||CHR(10)||'SQL:'||l_sql_stmt);
END;
/

show errors
pause

/*The following is a test script for the T_LOCK trigger.  First you must create a record definition in PeopleSoft Application Designer for record DMK in PeopleSoft (you don't need to build it) and but make field a key.  The trigger also benefits from the Function based index PSZRECDEFN*/

CREATE INDEX pszpsrecdefn ON psrecdefn (DECODE(sqltablename,' ','PS_'||recname,sqltablename))
TABLESPACE PSINDEX PCTFREE 0;

BEGIN
    sys.dbms_stats.gather_table_Stats
      (ownname => 'SYSADM'
      ,tabname => 'PSRECDEFN'
      ,estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE
      ,method_opt => 'FOR COLUMNS SIZE REPEAT'
      ,cascade => TRUE
      );
END;
/

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'T_LOCK';
--ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';

ALTER TRIGGER T_LOCK ENABLE;
DROP /*this should error*/ INDEX pszpsrecdefn;

/*RENAME test*/
ALTER /*this should error*/ TABLE PSRECDEFN RENAME TO WIBBLE;
RENAME /*this should error*/ PSRECDEFN TO WIBBLE;

/*Index Test*/
ALTER TRIGGER T_LOCK DISABLE;
DROP TABLE PS_DMK;
CREATE TABLE PS_DMK (DESCR VARCHAR2(30) NOT NULL);
ALTER TRIGGER T_LOCK ENABLE;
CREATE UNIQUE INDEX PS_DMK ON PS_DMK(DESCR);
DROP INDEX PS_DMK;
CREATE UNIQUE INDEX PS1DMK ON PS_DMK(DESCR);
DROP INDEX PS1DMK;
CREATE UNIQUE INDEX WIBBLE ON PS_DMK(DESCR);
DROP /*this should error*/ INDEX WIBBLE;
ALTER TRIGGER T_LOCK DISABLE;
DROP INDEX WIBBLE;
ALTER TRIGGER T_LOCK ENABLE;
CREATE UNIQUE INDEX PSZDMK ON PS_DMK(DESCR);
DROP /*this should error*/ INDEX PSZDMK;
ALTER TRIGGER T_LOCK DISABLE;
DROP INDEX PSZDMK;
ALTER TRIGGER T_LOCK ENABLE;

/*FBI test*/
ALTER TRIGGER T_LOCK DISABLE;
DROP TABLE PS_DMK;
CREATE TABLE PS_DMK (DESCR VARCHAR2(30) NOT NULL);
ALTER TRIGGER T_LOCK ENABLE;
CREATE INDEX PSZDMK ON PS_DMK(UPPER(DESCR));
DROP /*this should error*/ INDEX PSZDMK;
DROP /*this should error*/ TABLE PS_DMK;
ALTER TRIGGER T_LOCK DISABLE;
DROP INDEX PSZDMK;
ALTER TRIGGER T_LOCK ENABLE;
DROP TABLE PS_DMK;

/*PK TEST*/
ALTER TRIGGER T_LOCK DISABLE;
DROP TABLE PS_DMK;
CREATE TABLE PS_DMK (DESCR VARCHAR2(30) NOT NULL);
ALTER TRIGGER T_LOCK ENABLE;
CREATE UNIQUE INDEX PS_DMK ON PS_DMK(DESCR);
ALTER TABLE PS_DMK ADD CONSTRAINT PS_DMK PRIMARY KEY (DESCR) USING INDEX PS_DMK;
DROP /*this should ORA-02429*/ INDEX PS_DMK;
DROP /*this should error*/ TABLE PS_DMK;

/*MV LOG TEST*/
ALTER TRIGGER T_LOCK DISABLE;
DROP TABLE PS_DMK;
CREATE TABLE PS_DMK (DESCR VARCHAR2(30) NOT NULL);
ALTER TRIGGER T_LOCK ENABLE;
CREATE MATERIALIZED VIEW LOG ON PS_DMK WITH ROWID;
DROP /*this should error*/ TABLE PS_DMK;

/*IOT LOG TEST*/
ALTER TRIGGER T_LOCK DISABLE;
DROP TABLE PS_DMK;
ALTER TRIGGER T_LOCK ENABLE;
CREATE TABLE PS_DMK 
(DESCR VARCHAR2(30) NOT NULL
,CONSTRAINT PS_DMK PRIMARY KEY (DESCR)
) ORGANIZATION INDEX;
DROP /*this should error*/ TABLE PS_DMK;

/*GTT TEST*/
ALTER TRIGGER T_LOCK DISABLE;
DROP TABLE PS_DMK;
ALTER TRIGGER T_LOCK ENABLE;
CREATE GLOBAL TEMPORARY TABLE PS_DMK 
(DESCR VARCHAR2(30) NOT NULL);
DROP /*this should error*/ TABLE DMK;

/*PARTITION TEST*/
ALTER TRIGGER T_LOCK DISABLE;
DROP TABLE PS_DMK;
ALTER TRIGGER T_LOCK ENABLE;
CREATE TABLE PS_DMK (DESCR VARCHAR2(30) NOT NULL)
PARTITION BY RANGE(DESCR)
(PARTITION PS_DMK_1 VALUES LESS THAN ('X')
,PARTITION PS_DMK_2 VALUES LESS THAN (MAXVALUE)
);
CREATE INDEX PS_DMK ON PS_DMK(DESCR) LOCAL;
DROP /*this should error*/ INDEX PS_DMK;
DROP /*this should error*/ TABLE PS_DMK;

/*clear up afer test*/
ALTER TRIGGER T_LOCK DISABLE;
DROP TABLE PS_DMK;
ALTER TRIGGER T_LOCK ENABLE;
--ALTER SESSION SET SQL_TRACE = FALSE;

spool off