Oracle: check the existance 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…

Bookmark and Share

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>