CODEWITHSUNDEEP
sqldatabasetriggers
pencilCake
pencilCake

Reputation: 53223

Is there a way to see if any column is modified by a trigger in a SQL Db?

Is there a way to query the Db to see what triggers are interacting with dbo.Table1.FooColumn in the DataBase I am working on? I need to investigate what triggers are possibly modifying "FooColumn" column on Table1.

Thanks

Upvotes: 0

Views: 38

Answers (1)

Chris Morgan
Chris Morgan

Reputation: 2080

For MySQL, Postgres, or SQL Server, you can use INFORMATION_SCHEMA.TRIGGERS:

SELECT * FROM INFORMATION_SCHEMA.TRIGGERS
WHERE EVENT_OBJECT_SCHEMA = 'dbo'
AND (EVENT_OBJECT_TABLE = 'Table1' OR LCASE(ACTION_STATEMENT) LIKE '%table1%')
AND LCASE(ACTION_STATEMENT) LIKE '%foocolumn%';

Oracle doesn't support the ANSI INFORMATION_SCHEMA, so you could instead use:

SELECT * FROM DBA_TRIGGERS
WHERE (TABLE_NAME = 'Table1' OR LCASE(TRIGGER_BODY) LIKE '%table1%')
AND LCASE(TRIGGER_BODY) LIKE '%foocolumn%';

The above queries look for triggers defined for Table1 and affect FooColumn or triggers that are defined for another table but affect Table1.FooColumn.

Note that if the DBMS supports triggers that can call stored procedures (like Oracle), you'll have to manually inspect all the stored procedures called by a trigger.

Upvotes: 2

Related Questions