Retrieving catalog information about triggers

The SYSIBM.SYSTRIGGERS table contains information about triggers.

Procedure

To retrieve catalog information about triggers:

Begin general-use programming interface information.

Query the SYSIBM.SYSTRIGGERS table to obtain information about the triggers defined in your databases.

You can issue this query to find all the triggers defined on a particular table, their characteristics, and to determine the order they are activated in:

SELECT DISTINCT SCHEMA, NAME, TRIGTIME, TRIGEVENT, GRANULARITY, CREADEDTS
   FROM SYSIBM.SYSTRIGGERS
   WHERE TBNAME = 'EMP' AND TBOWNER = 'DSN8C10';

Issue this query to retrieve the text of a particular trigger:

SELECT STATEMENT, CREATEDTS
   FROM SYSIBM.SYSTRIGGERS
   WHERE SCHEMA = schema_name
      AND NAME = trigger_name
   ORDER BY CREATEDTS;

Issue this query to determine triggers that must be rebound because they are invalidated after objects are dropped or altered:

SELECT COLLID, NAME
   FROM SYSIBM.SYSPACKAGE
   WHERE TYPE = 'T'
      AND (VALID = 'N' OR OPERATIVE = 'N');

End general-use programming interface information.