13.5.4 Trigger management

Organization of this subsection
(1) Trigger definition
(2) Re-creating a trigger SQL object

(1) Trigger definition

When a trigger is defined, all existing functions, procedures, and trigger SQL objects that the table uses become invalid and have to be re-created. By referencing the SQL_ROUTINE_RESOURCES dictionary table before a trigger is defined, you can check the functions, procedures, and trigger SQL objects that will become invalid. Check the SQL objects that will become invalid so that you can re-create them.

(a) Checking the functions, procedures, and trigger SQL objects that will become invalid when a trigger is defined

The following example shows how to check the functions, procedures, and trigger SQL objects that will become invalid when a trigger definition is defined. In the case of a trigger, what will become invalid is the trigger identifier (TRIGGER_NAME). In the case of functions and procedures, TRIGGER_NAME becomes NULL.

SELECT DISTINCT B.ROUTINE_SCHEMA, B.ROUTINE_NAME, B.SPECIFIC_NAME, A.TRIGGER_NAME
FROM MASTER.SQL_ROUTINE_RESOURCES B LEFT JOIN MASTER.SQL_TRIGGERS A
ON B.ROUTINE_SCHEMA=A.TRIGGER_SCHEMA
AND B.SPECIFIC_NAME=A.SPECIFIC_NAME
WHERE B.BASE_TYPE='R'
AND B.BASE_OWNER='authorization-identifier-of-owner-of-table-for-which-trigger-is-defined'
AND B.BASE_NAME='table-for-which-trigger-is-defined'
AND (B.column-name* ='Y'
 OR (  B.INSERT_OPERATION IS NULL
   AND B.UPDATE_OPERATION IS NULL
   AND B.DELETE_OPERATION IS NULL))

* To retrieve the SQL objects that will become invalid when a trigger is defined with INSERT as the triggering event, specify INSERT_OPERATION as the column name; when UPDATE is the triggering event, specify UPDATE_OPERATION; and when DELETE is the triggering event, specify DELETE_OPERATION.

(2) Re-creating a trigger SQL object

When a table, index, or other resource already used by a trigger is defined, modified, or deleted, the trigger SQL objects become invalid. Also, defining or deleting indexes for a table that a trigger is using will cause the index information for the trigger SQL objects to become invalid.

If a trigger SQL object becomes invalid, or if the SQL object's index information becomes invalid, the trigger event SQL statement cannot be executed. To prevent a trigger SQL object or the SQL object's index information from becoming invalid, the trigger SQL objects must be re-created with the ALTER TRIGGER or ALTER ROUTINE definition SQL statements.

(a) How to check the resources used by a trigger

You can check information on the resources that a trigger is using by referencing the SQL_ROUTINE_RESOURCES, SQL_TRIGGER_USAGE, and SQL_ROUTINE_PARAMS dictionary tables.

(b) How to check triggers that will be deleted before deleting columns in a table

If all columns that act as triggering events are deleted, the trigger will be deleted. The following is an example of an SQL for checking the triggers that will be deleted before deleting columns from a table:

SELECT A.TRIGGER_SCHEMA, A.TRIGGER_NAME
 FROM MASTER.SQL_TRIGGERS A
WHERE A.N_UPDATE_COLUMNS>0
 AND A.TABLE_SCHEMA='authorization-identifier-of-owner-of-table-from-which-columns-will-be-deleted'
 AND A.TABLE_NAME='table-identifier-of-table-from-which-columns-will-be-deleted'
 AND NOT EXISTS(SELECT * FROM MASTER.SQL_TRIGGER_COLUMNS B
                WHERE B.TRIGGER_SCHEMA=A.TRIGGER_SCHEMA
                  AND B.TRIGGER_NAME=A.TRIGGER_NAME
                  AND B.TABLE_SCHEMA=A.TABLE_SCHEMA
                  AND B.TABLE_NAME=A.TABLE_NAME
                  AND B.COLUMN_NAME NOT IN('name-of-column-to-be-deleted', ...))

(c) How to check the functions, procedures, and trigger SQL objects or SQL object index information that will become invalid before defining, modifying, or deleting a table or index

The following is an SQL example of checking for the functions, procedures, and trigger SQL objects or SQL object index information that will become invalid before defining, modifying, or deleting a table or index. If a trigger will become invalid, the trigger identifier (TRIGGER_NAME) is obtained. If it is a function or a procedure, the value of TRIGGER_NAME becomes NULL.

(d) How to check the functions, procedures, and trigger SQL objects or SQL object index information that has become invalid as a result of defining, modifying, or deleting a table or index

To check the trigger SQL objects or SQL object index information that have become invalid because of definition, modification, or deletion of a table or index, refer to the TRIGGER_VALID and INDEX_VALID columns of the SQL_TRIGGER dictionary table. If the entry in the TRIGGER_VALID column is N, the trigger SQL object has become invalid. If the entry in the INDEX_VALID column is N, the index information of that trigger SQL object has become invalid.

The following is an SQL example of checking for the functions, procedures, and trigger SQL objects and SQL object index information that has become invalid because of definition, modification, or deletion of a table or index. If a trigger has become invalid, the trigger identifier (TRIGGER_NAME) is obtained. For functions and procedures, the value of TRIGGER_NAME becomes NULL.

SELECT 'TRIGGER', TRIGGER_SCHEMA AS "SCHEMA", TRIGGER_NAME AS "NAME",
TRIGGER_VALID AS "OBJECT_VALID", INDEX_VALID
 FROM MASTER.SQL_TRIGGERS
WHERE TRIGGER_VALID='N' OR INDEX_VALID='N'
UNION
SELECT 'ROUTINE', ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_VALID, INDEX_VALID
 FROM MASTER.SQL_ROUTINES
WHERE ROUTINE_VALID='N' OR INDEX_VALID='N'