Re: AFTER CREATE trigger (object actually being REPLACED)

On Nov 29, 5:52 pm, DA Morgan <damor..._at_psoug.org> wrote:
> joel garry wrote:
> > On Nov 29, 10:34 am, "Syltrem" <syltremz..._at_videotron.ca> wrote:
> >> Hi
>
> >> I have an AFTER CREATE trigger.
> >> I can verify what kind of object has been created by using the
> >> ora_dict_obj_type event attribute function, but is there such a function
> >> that would tell me if the object is being added *or* replaced ?
>
> >> The object here is a VIEW, and a CREATE OR REPLACE does not always create
> >> the view... in such case I don't want to do anything but the trigger gets
> >> fired anyway.
>
> >> Or do I have to query DBA_OBJECTS to check CREATED and LAST_DDL_TIME to make
> >> an educated guess ?
>
> >> Or any better way that you know of...
>
> > I'm confused - the docs say you can't use an after trigger on a view?
> > I'm guessing you are seeing an undefined result that should have
> > thrown an error on trigger creation? Maybe it will be clear if you
> > give example code.
>
> >http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statem...
>
> > jg
> > --
> > @home.com is bogus.
> > Lock up your kids! http://www.knbc.com/news/14720151/detail.html
>
> The trigger referred to by Syltrem is an AFTER DDL EVENT TRIGGER. They
> are not "on the view" they are on the DATABASE or SCHEMA.
>
> http://www.psoug.org/reference/ddl_trigger.html

Thanks for making that clear, I certainly misread the doc.

This seems to answer Syltrem's question - it fires because the event happened, not because the event - object creation - succeeded. That ought to be documented, don't you think? Especially when it does say "CREATE Specify CREATE to fire the trigger whenever a CREATE statement adds a new database object to the data dictionary."

I'd go as far as to say this is a doc bug or maybe a real bug, if the OP is true (no offense intended). But of course, I'm willing to be corrected if I still am confused. We need a demo of the OP.

jg

--
@home.com is bogus.
"You run these scripts and all the kernel parameters and so on are
automatically set for you -and automation therefore m2 article at all.
But that is the non-scalable approach: once you try to scale by
adopting the 'universal' approach, you have a new set of
considerations to take into account, chief amongst which is the need
to ensure new work doesn't break the earlier stuff.eans I don't have
to document which parameters to set or how to set them. " - hjr