Accenture Enkitec Group E4 Webinar

Tuesday, April 25, 2006

Using DDL Triggers to protect database objects not managed by Application Designer

Sometimes it is necessary create certain database objects manually, and not manage them via the Application Designer. However, this can introduce some management problems. When an object is altered in the Application Designer, it is then necessary to build an alter script that may rebuild the whole table. It is easy for the additional objects to be accidentally lost. There are two main scenarios.
  • Additional indexes and Function based indexes: During the course of performance tuning it is often necessary to build additional indexes. Ideally these indexes should be added via the Application Designer. However, a DBA might add an index directly to the production environment, and it may take time to get a project moved into that environment. The other scenario, is that the Application Designer cannot build function-based indexes. These are particularly useful to build upper case indexes on columns, to support case-insensitive searching.
  • PeopleSoft delivers a mechanism to build triggers to perform DML auditing. However, if you then rebuild the underlying table, then the trigger will be lost, and there is nothing PeopleSoft to warn you of this, or to audit this situation.

My solution to both scenarios is to create a DDL trigger to prevent accidental ALTER and DROP commands on objects not defined by PeopleTools. When you do want to alter this objects you can then disable this trigger. So you now have a way of controlling when you can and can't drop additional triggers and indexes. You still have to remmeber to switch this trigger back on again!

CREATE OR REPLACE TRIGGER t_lock 
BEFORE DROP OR ALTER
ON SYSADM.SCHEMA
DECLARE
l_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;
BEGIN
IF ora_dict_obj_type = 'TABLE' THEN
SELECT r.recname
INTO l_recname
FROM psrecdefn r
WHERE DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename) = ora_dict_obj_name
;

BEGIN
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 l_generate_message;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;

BEGIN
SELECT 'Index '||i.index_name||' on table '||ora_dict_obj_name||' is managed outside PeopleTools.'
INTO 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 EXISTS(
SELECT 'x'
FROM psrecdefn r, psrecfielddb p, psrecfield f, psindexdefn j
WHERE DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename) = i.table_name
AND r.recname = p.recname
AND p.fieldname = f.fieldname
AND (f.recname = p.recname_parent
OR (MOD(f.useedit/1,2)=1 /*key*/
OR MOD(f.useedit/2,2)=1 /*dup*/
OR MOD(f.useedit/16,2)=1 /*alt*/))
AND j.recname = p.recname_parent
AND 'PS'||j.indexid||r.recname = i.index_name
)
;
RAISE l_generate_message;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;

ELSIF ora_dict_obj_type = 'TRIGGER' THEN

BEGIN
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 l_generate_message;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;

ELSIF ora_dict_obj_type = 'INDEX' THEN

BEGIN
SELECT 'Index '||i.index_name||' on table '||ora_dict_obj_name||' is managed outside PeopleTools.'
INTO l_msg
FROM all_indexes i
WHERE ROWNUM = 1
AND i.index_name = ora_dict_obj_name
AND i.owner = ora_dict_obj_owner
AND NOT EXISTS(
SELECT 'x'
FROM psrecdefn r, psrecfielddb p, psrecfield f, psindexdefn j
WHERE DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename) = i.table_name
AND r.recname = p.recname
AND p.fieldname = f.fieldname
AND (f.recname = p.recname_parent
OR (MOD(f.useedit/1,2)=1 /*key*/
OR MOD(f.useedit/2,2)=1 /*dup*/
OR MOD(f.useedit/16,2)=1 /*alt*/))
AND j.recname = p.recname_parent
AND 'PS'||j.indexid||r.recname = i.index_name
)
;
RAISE l_generate_message;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;

END IF;

EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN l_generate_message THEN
Raise_application_error(-20042,'T_LOCK: '||l_msg||' '||l_msg2);
END;
/

Notes:

  • The trigger performs a number of test queries on PeopleTools or Oracle Catalogue views. There are different queries depending upon the type of object being ALTERed or DROPped. If a query returns a row then there is a problem, and an exception is raised. The queries also generate part of the error message that is returned.
  • The trigger only affects operations on tables that are specified in PSRECDEFN as type 0 or 7 records. I have not added an handling for the additional instances of a PeopleSoft temporary table.
  • The ora_% variables are described in the RDBMS documentation in Application Developer's Guide - Fundamentals. In the 9.2 documentation this is in chapter 16. Working with System Events Event Attribute Functions

So here are a few tests. I have created a function based index and a two triggers on PS_RT_RATE_TBL. I have chosen this table for the example because it also has a PeopleSoft generated trigger for Mobile agents.

CREATE INDEX DMK_RT_RATE_TBL 
ON PS_RT_RATE_TBL(UPPER(RT_RATE_INDEX));
Index created.
CREATE OR REPLACE TRIGGER pstrt_rate_tbl
AFTER INSERT OR UPDATE OR DELETE
ON ps_rt_rate_tbl
FOR EACH ROW
BEGIN
raise_application_error(-20042,'No DML on PS_RT_RATE_TBL');
END;
/
Trigger created.
CREATE OR REPLACE TRIGGER psurt_rate_tbl
AFTER INSERT OR UPDATE OR DELETE
ON ps_rt_rate_tbl
FOR EACH ROW
BEGIN
raise_application_error(-20042,'No DML on PS_RT_RATE_TBL');
END;
/
Trigger created.

So the t_lock trigger prevents me from droping or altering either the function based index or the table.

DROP TABLE PS_RT_RATE_TBL
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20042: T_LOCK: Trigger PSTRT_RATE_TBL exists on table PS_RT_RATE_TBL. Cannot DROP table
SYSADM.PS_RT_RATE_TBL
ORA-06512: at line 99

ALTER TABLE PS_RT_RATE_TBL RENAME to DMK
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20042: T_LOCK: Trigger PSTRT_RATE_TBL exists on table PS_RT_RATE_TBL. Cannot ALTER table
SYSADM.PS_RT_RATE_TBL
ORA-06512: at line 99

DROP INDEX DMKZRT_RATE_TBL
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20042: T_LOCK: Index DMKZRT_RATE_TBL is managed outside PeopleTools. Cannot DROP index
SYSADM.DMKZRT_RATE_TBL
ORA-06512: at line 99

But I can drop any other index on the table that is maintained by PeopleTools.

DROP INDEX PS_RT_RATE_TBL
Index dropped.

I can't alter any trigger on the table except the PSU trigger that is created by Application Designer for objects that are maintained by mobile agents.

ALTER TRIGGER PSTRT_RATE_TBL DISABLE
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20042: T_LOCK: Trigger PSTRT_RATE_TBL exists on PeopleSoft record RT_RATE_TBL. Cannot ALTER trigger
SYSADM.PSTRT_RATE_TBL
ORA-06512: at line 99

DROP TRIGGER PSTRT_RATE_TBL
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20042: T_LOCK: Trigger PSTRT_RATE_TBL exists on PeopleSoft record RT_RATE_TBL. Cannot DROP trigger
SYSADM.PSTRT_RATE_TBL
ORA-06512: at line 99

ALTER TRIGGER PSURT_RATE_TBL DISABLE;
Trigger altered.

DROP TRIGGER PSURT_RATE_TBL;
Trigger dropped.

No comments :