Re: AFTER CREATE trigger (object actually being REPLACED)
Date: Fri, 23 May 2008 11:38:22 -0400
Message-ID: <RxBZj.148962$Gv.131356@en-nntp-07.dc1.easynews.com>
"joel garry" <joel-garry_at_home.com> wrote in message
news:68c1ed33-2484-4f90-9077-c528359096d0_at_b40g2000prf.googlegroups.com...
> 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
>
Finally I'm back on this
CREATE OR REPLACE TRIGGER WEBP0120INF
AFTER CREATE
ON SCHEMA
BEGIN
IVADBA.IVAP0121IVA('INFWEBUSER');
END;
/
Create or Replace Procedure IVAP0121IVA
(p_New_Grantee varchar2)
Authid Current_User
as
...
...
BEGIN
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);
END;
/
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