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
SELECT table_name, trigger_name
AND trigger_name NOT LIKE table_name||'_T'
ORDER BY table_name;
SELECT tbname, name
AND name NOT LIKE tbname||'_T'
AND name NOT LIKE tbname||'_U'
ORDER BY tbname;
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]
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';