Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to check if a trigger is invalid?

I'm working on databases that have moving tables auto-generated by some obscure tools. By the way, we have to track information changes in the table via some triggers. And, of course, it occurs that some changes in the table structure broke some triggers, by removing a column or changing its type, for example.

So, the question is: Is there a way to query the Oracle metadata to check is some triggers are broken, in order to send a report to the support team?

The user_triggers give all the triggers and tells if they are enable or not, but does not indicate if they are still valid.

like image 288
gizmo Avatar asked Sep 23 '08 13:09

gizmo


1 Answers

SELECT *
FROM   ALL_OBJECTS
WHERE  OBJECT_NAME = trigger_name
AND    OBJECT_TYPE = 'TRIGGER'
AND    STATUS <> 'VALID'
like image 170
cagcowboy Avatar answered Sep 19 '22 09:09

cagcowboy