Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Trigger at the Schema level - not working

Re: Trigger at the Schema level - not working

From: Jawahar Rajan <jrajan_at_nc.rr.com>
Date: Wed, 31 Dec 2003 20:40:42 GMT
Message-ID: <evGIb.236992$ea%.178270@news01.bloor.is.net.cable.rogers.com>


All, Thanks for your replies
I thought that schema level triggrers would be helpful during the development of the database. So that any table changes or creation would be caught and the audit table created automatically. I forgot that the audit table itself is a table which will trigger another event and so on.

Why does oracle have schema level triggers, where would one use them?

Also to carry out an audit trail how could I build this into the schema. Is is possible to use dynamic SQL in Oracle 9i and run a script to create the audit tables when change are made during development. Are there any good sites devoted to database auditing.

Thanks
Jawahar

"Sybrand Bakker" <gooiditweg_at_sybrandb.demon.nl> wrote in message news:vdpguvkr4etg4td4s9fqmp2qo9t6pnmgem_at_4ax.com...
> On 23 Dec 2003 07:25:23 -0800, rgaffuri_at_cox.net (Ryan Gaffuri) wrote:
>
> >Thomas Kellerer <spam_eater_at_gmx.net> wrote in message
news:<bs90lb$apksk$1_at_ID-13919.news.uni-berlin.de>...
> >> Jawahar Rajan schrieb:
> >>
> >> > All,
> >> > I am trying to use the following trigger. It is a Schema level
trigger, that
> >> > should run each time a table is created.
> >> > I am trying to create a shadow table for eachnew table created.
However I
> >> > get an error that say I do not have permissions?
> >> >
> >> > Any ideas why
> >> > The error:
> >> > ERROR at line 1:
> >> > ORA-00604: error occurred at recursive SQL level 1
> >> > ORA-01031: insufficient privileges
> >> > ORA-06512: at line 39
> >> >
> >> > Do I need tobe a DBA to get the trigger to fire?
> >> > Any Suggestions welcome
> >> >
> >> > Jawahar
> >>
> >> I'm not familiar with schema level triggers, so maybe there is
something I'm
> >> missing, but wouldn't your trigger cause an infinite loop?
> >>
> >> You are creating a table in there which in turn, will fire the trigger
which
> >> will create a shadow table for your shadow table and so on...
> >>
> >> Thomas
> >
> >
> >look at line 39 of your trigger code. what do you have insufficient
trigger on?
> >
> >ORA-06512: at line 39
> >
> >work from there.
>
> Likely he has create table privilege through the connect role. He
> needs it directly. Other than that I would never ever think such a
> policy would be feasible. Likely he is up for disaster
>
>
> --
> Sybrand Bakker, Senior Oracle DBA
Received on Wed Dec 31 2003 - 14:40:42 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US