February 20, 2013

Find all custom triggers on Maximo tables

Before Maximo 6 it was a common practice to implement customizations using database triggers. Although Conditional Expressions, SigOptions, Java coding and scripting are now available in Maximo 7, I still see many Maximo environments with a lot of database triggers.
In a recent project I have found a Maximo system with more than 30 triggers and many of these were used to send emails to specific users. Since those emails were sent synchronously during inserts and updates, they were causing lots of performance issues.
For consultants like me it may be useful to have a set of SQL statements to run against a Maximo database to have a quick grasp of the level of customizations.

Here are the statements I have developed to find all the custom triggers defined on Maximo tables. The tag [MXSCHEMA] must be replaced with the schema where Maximo tables are stored.
NOTE: The queries are not 100% accurate because some built-in triggers does not comply to the naming convention [TABLENAME]_T

Oracle

SELECT table_name, trigger_name
FROM all_triggers
WHERE owner='[MXSCHEMA]'
AND trigger_name NOT LIKE table_name||'_T'
AND status='ENABLED'
ORDER BY table_name;


IBM DB2

SELECT tbname, name
FROM sysibm.systriggers
WHERE schema='[MXSCHEMA]'
AND name NOT LIKE tbname||'_T'
AND name NOT LIKE tbname||'_U'
ORDER BY tbname;


SQL Server

SELECT
  sysobjects.name AS trigger_name
  ,USER_NAME(sysobjects.uid) AS trigger_owner
  ,s.name AS table_schema
  ,OBJECT_NAME(parent_obj) AS table_name
  ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate
  ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete
  ,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert
  ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter
  ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof
  ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled]
FROM sysobjects
INNER JOIN sys.tables t ON sysobjects.parent_obj = t.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE sysobjects.type = 'TR';

Thanks MyGeekDaddy

1 comment:

  1. SQL Server:

    SELECT
    sysobjects.name AS trigger_name
    ,USER_NAME(sysobjects.uid) AS trigger_owner
    ,s.name AS table_schema
    ,OBJECT_NAME(parent_obj) AS table_name
    ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate
    ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete
    ,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert
    ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter
    ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof
    ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled]
    FROM sysobjects

    INNER JOIN sys.tables t
    ON sysobjects.parent_obj = t.object_id

    INNER JOIN sys.schemas s
    ON t.schema_id = s.schema_id
    WHERE sysobjects.type = 'TR'

    ReplyDelete