Re: AFTER CREATE trigger (object actually being REPLACED)

From: Syltrem <>
Date: Fri, 23 May 2008 11:38:22 -0400
Message-ID: <RxBZj.148962$>

"joel garry" <> wrote in message
> On Nov 29, 5:52 pm, DA Morgan <> wrote:

>> joel garry wrote:
>> > On Nov 29, 10:34 am, "Syltrem" <> 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.
>> >
>> > jg
>> > --
>> > is bogus.
>> > Lock up your kids!
>> 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.

> 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
> --
> 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

Finally I'm back on this


Create or Replace Procedure IVAP0121IVA

        (p_New_Grantee varchar2)
Authid Current_User

        ivadba.ivap0001std('%IVAP0121IVA-I-CRETBL, user '
                || v_OS_Username || ' creates ' || ora_sysevent || ' '
                || ora_dict_obj_type || ' '
                || ora_dict_obj_owner || '.' || ora_dict_obj_name
                || ' using ' || IVAP0010STD.FN_Get_Pgm_Name(v_program),
                        'I', false);


The ivadba.ivap0001std outputs a record in my "log file".

ora_sysevent has the value 'CREATE' whether the view already exists or not.

If I do this command twice, the second time it does not actually create the view, it replaces it :
CREATE OR REPLACE VIEW XXX AS SELECT * FROM THISTABLE; To correctly reflect this, ora_sysevent should not say 'CREATE' but 'REPLACE' or something else the 2nd time.

I currently know of no way to tell if the view was there. Or maybe I should have both a BEFORE and an AFTER trigger. Before to tell if its being replaced or created (I can lookup the dictionary views), and after to do whatever I need to do with it after it's created or replaced.

But this is inconvenient I think.
Syltrem Received on Fri May 23 2008 - 10:38:22 CDT

Original text of this message