Re: Trigger: AFTER CREATE ON SCHEMA doesnt fire

From: Pete Finnigan <plsql_at_petefinnigan.com>
Date: Fri, 17 Sep 2004 10:40:35 +0100
Message-ID: <BMjsJQBTErSBRx2z@peterfinnigan.demon.co.uk>

Hi,

Check if the hidden parameter _system_trig_enabled is still set to TRUE. If this is set to false then system triggers will no longer fire. You cannot use "show parameter" in SQL*Plus or select from v$parameter. You need to use some x$ views so you need access to the SYS schema. The following SQL will get the value for you:

SQL> l

  1  select     x.ksppinm  name,
  2     y.ksppstvl  value,
  3     ksppdesc description
  4  from       x$ksppi x,
  5     x$ksppcv y
  6  where      x.inst_id = userenv('Instance')
  7  and        y.inst_id = userenv('Instance')
  8  and        x.indx = y.indx
  9* and     x.ksppinm = '_system_trig_enabled'

SQL> / NAME

---

VALUE

---

DESCRIPTION

---

_system_trig_enabled
TRUE
are system triggers enabled

SQL> hth

Kind regards

Pete

-- 
Pete Finnigan
email:pete_at_petefinnigan.com
Web site: http://www.petefinnigan.com - Oracle security audit specialists
Book:Oracle security step-by-step Guide - see http://store.sans.org for details.

Received on Fri Sep 17 2004 - 04:40:35 CDT