Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Trigger at the Schema level - not working
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