Re: SYS can't own triggers?!

From: Daniel Morgan <dmorgan_at_exesolutions.com>
Date: Fri, 16 Aug 2002 16:36:42 GMT
Message-ID: <3D5D2A13.F5F6AA74@exesolutions.com>

Thomas Kyte wrote:

> In article <3D5BC10D.A2A3A811_at_exesolutions.com>, Daniel says...
> >
> >Anurag Varma wrote:
> >
> >> They'll probably be others who will ask you:
> >> Why are you trying to create triggers on SYS objects?
> >> Its probably a catch-22 but a person who does not know that
> >> he cannot create triggers on SYS objects is the last person who
> >> should be creating triggers on SYS objects (i.e. if it were possible)
> >>
> >> :)) Sorry. Could not help myself.
> >>
> >> I don't know the complete answer but I can imagine a scenario when
> >> a before insert trigger is created on obj# table which changes the name
> >>of the object before inserting. DB will quickly die down. Why somebody would do
> >>this ..
> >> well ..
> >>
> >> There can be so many other scenarios .. you just use your imagination.
> >>
> >> Anurag
> >>
> >> "Peter Chatterton" <peter_at_chatterton.name> wrote in message
> >> news:WlD69.6257$2T2.1099477_at_news20.bellglobal.com...
> >> > I ran the wrong script by mistake and got an ORA-04089.
> >> > The manual very helpfully says:
> >> >
> >> > ORA-04089 cannot create triggers on objects owned by SYS
> >>> Cause: An attempt was made to create a trigger on an object owned by
> >> > SYS.
> >> > Action: Do not create triggers on objects owned by SYS.
> >> >
> >> > Can someone explain the sense, if any, behind this; hopefully in simple
> >> > terms?
> >> >
> >> > Thanks,
> >> > Peter.
> >> >
> >> >
> >> >
> >> >
> >
> >Glad you couldn't help yourself from stating the obvious.
> >
> >The reason is that there is nothing you can do with a trigger on a SYS object
> >that won't
> >threaten the integrity, performance and scalability of the database, nor is
> >there any
> >reason to create one. Whatever you can accomplish with a trigger on a SYS object
> >can be
> >accomplished by other means.
> >
> >The one exception to the rule is SYSTEM triggers such as AFTER LOGON that must
> >be owned by
> >SYS.
> >

>

> And that isn't an exception either.

>

> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create or replace trigger after_logon
> 2 after logon on database
> 3 begin
> 4 null;
> 5 end;
> 6 /

>

> Trigger created.

>

> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> show user
> USER is "OPS$TKYTE"
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM>

>

> DON'T create anything, nothing, nada, nunca -- as SYS. Even these triggers!

>

> >Daniel Morgan
> >

>


> --

> Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/

> Expert one on one Oracle, programming techniques and solutions for Oracle.
> http://www.amazon.com/exec/obidos/ASIN/1861004826/
> Opinions are mine and do not necessarily reflect those of Oracle Corp

You are correct.

I don't know why I thought otherwise. Saturday's lecture will include a mea culpa.

Thanks.

Daniel Morgan Received on Fri Aug 16 2002 - 11:36:42 CDT