CODEWITHSUNDEEP
sqldatabasedb2
user1340582
user1340582

Reputation: 19729

How to search for triggers in a schema in DB2 9.7?

I have a list of tables in a schema in IBM DB2 9.7. Some of them have triggers and others do not. I am creating a stored procedure that goes through all tables in SYSCAT.TABLES, but how do I check with sql syntax if that table has a trigger with a specific name? (or any trigger)

Upvotes: 7

Views: 21427

Answers (1)

bhamby
bhamby

Reputation: 15469

You can use the SYSCAT.TRIGGERS catalog view.

SELECT *
FROM SYSCAT.TRIGGERS
WHERE TABNAME    = @table_name
  AND TABCREATOR = @table_schema
  AND TRIGNAME   = @trigger_name
  AND TRIGSCHEMA = @trigger_schema

The predicates given are just examples of columns you might search by, you can obviously pick and choose based on your needs.

Upvotes: 14

Related Questions