trigger

Oracle: check the existance of logon/logoff triggers

Friday, July 25th, 2008

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…