Oracle: check the existence of logon/logoff triggers


select
decode((count(trigger_name)),0,'LOGON trigger missing',
'Number of logon triggers: ' || count(trigger_name) ) "INFO"
from sys.dba_triggers
where
TRIGGERING_EVENT like 'LOGON%'
and status='ENABLED'
and owner='SYS'
union
select
decode((count(trigger_name)),0,'LOGOFF trigger missing','',
'Number of logoff triggers:' || count(trigger_name)) "INFO"
from sys.dba_triggers
where
TRIGGERING_EVENT like 'LOGOFF%'
and status='ENABLED'
and owner='SYS'

The field TRIGGERING_EVENT could have the spaces at the end! Very clever…

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.