rem t_lock_test.sql: Script to test T_LOCK trigger
rem (c) Go-Faster Consultancy Ltd.

spool t_lock_test

/*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*/
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;

ALTER TABLE PS_DMK TRUNCATE PARTITION PS_DMK_1;

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

spool off