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.
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))
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.
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.
SELECT B.* FROM MASTER.SQL_TRIGGERS A, MASTER.SQL_TRIGGER_USAGE B
WHERE A.TRIGGER_SCHEMA='schema-name'
AND A.TRIGGER_NAME='trigger-identifier'
AND A.TRIGGER_SCHEMA=B.TRIGGER_SCHEMA
AND A.TRIGGER_NAME=B.TRIGGER_NAME
SELECT B.* FROM MASTER.SQL_TRIGGERS A, MASTER.SQL_ROUTINE_PARAMS B
WHERE A.TRIGGER_SCHEMA='schema-name'
AND A.TRIGGER_NAME='trigger-identifier'
AND A.TRIGGER_SCHEMA=B.ROUTINE_SCHEMA
AND A.SPECIFIC_NAME=B.SPECIFIC_NAME
SELECT B. * FROM MASTER.SQL_TRIGGERS A, MASTER.SQL_ROUTINE_RESOURCES B
WHERE A.TRIGGER_SCHEMA='schema-name'
AND A.TRIGGER_NAME='trigger-identifier'
AND A.TRIGGER_SCHEMA=B.ROUTINE_SCHEMA
AND A.SPECIFIC_NAME=B.SPECIFIC_NAME
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', ...))
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.
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 IN('R','V')
AND B.BASE_OWNER='table(view-table)-owner-authorization-identifier'
AND B.BASE_NAME='table(view-table)-identifier'
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 ='I'
AND B.BASE_OWNER='index-owner-authorization-identifier'
AND B.BASE_NAME='index-identifier'
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 ='P'
AND B.BASE_OWNER='function(procedure)-owner-authorization-identifier'
AND B.BASE_NAME='routine-identifier'
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 ='T'
AND B.BASE_OWNER='trigger-owner-authorization-identifier'
AND B.BASE_NAME='trigger-identifier'
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_OWNER='schema-name'
SELECT 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_NAME='identifier-of-data-type-to-be-deleted'
AND B.BASE_TYPE='D'
UNION
SELECT B.ROUTINE_SCHEMA, B.ROUTINE_NAME, B.SPECIFIC_NAME, A.TRIGGER_NAME
FROM MASTER.SQL_ROUTINES C INNER JOIN MASTER.SQL_ROUTINE_RESOURCES B
ON C.SPECIFIC_NAME=B.BASE_NAME
LEFT JOIN MASTER.SQL_TRIGGERS A
ON B.ROUTINE_SCHEMA=A.TRIGGER_SCHEMA
AND B.SPECIFIC_NAME=A.SPECIFIC_NAME
WHERE C.ROUTINE_ADT_OWNER='owner-authorization-identifier-of-user-defined-type-to-be-deleted'
AND C.ROUTINE_ADT_NAME='type-identifier-of-user-defined-type-to-be-deleted'
AND B.BASE_TYPE='P'
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'