If you delete a table, the triggers that are associated with the table are not deleted from the database, they become inoperative. When a trigger becomes inoperative, it is retained in the SYSCAT.TRIGGERS catalog, but its VALID column is set to X to indicate its inoperative state.

Even though you delete a table, you might want to use the triggers that are associated with the table in SQL procedures. If you run an SQL procedure that contains an inoperative trigger, the trigger is ignored. To restore the trigger functionality in the SQL procedure, you must re-create the inoperative trigger.

To re-create an inoperative trigger:

1. Open the DB2 Control Center. From the Tools menu, select IBM DB2 Tools, and then select DB2 Control Center.
2. In the DB2 Control Center, query the SYSCAT.TRIGGERS catalog for the inoperative trigger that you want to re-create.
3. In the query results, copy the trigger definition.

   The TEXT column of the query results contains the full text of the CREATE TRIGGER statement.

4. In the Server Explorer in Visual Studio, right-click your data connection, and then select New Script on the shortcut menu.

   The IBM Script Designer opens.

5. Paste the copied CREATE TRIGGER statement in the Script view of the designer.
6. Click Execute Script on the designer toolbar to run the script and re-create the trigger in the database.
7. Close the designer.

See Also

Managing Procedures, Functions, and Objects | Deleting Procedures, Functions, or Objects from an IBM Database | Running Procedures or Functions

.NET Development Forum   DB2 FAQs

© Copyright IBM Corporation 2002, 2019. All Rights Reserved.