Re: Need to write trigger for "adding a new table"

"Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message news:6_OdnfbAwK4BeLHfRVn-3w_at_comcast.com...
>
> <deanbrown3d_at_yahoo.com> wrote in message
> news:1110237417.408305.251240_at_g14g2000cwa.googlegroups.com...
>> But I can't add a trigger to ALL_TABLES because its a view. What to do?
>>
>>
>> I need to create an update-log table for our database, to write out
>> additions of (and changes to) any new tables, so that we can see what
>> changes have been made recently. This is to be used in conjunction with
>> the change-manager, so please don't go there. I am just not sure how to
>> write a trigger for a view. This particular view looks horrendous!
>>
>> THX!
>>
>> Dean
>>
>
> you want a database trigger
>
> see the CREATE TRIGGER ... BEFORE ... ddl_event syntax
>
> ++ mcs
>
>

PS: if you're thinking of creating a trigger on a view, and espcially a system (data dictionary) view, there's a couple of things you should be aware of:

1. creating an INSTEAD OF trigger on a view is legal, but it only works when the view is the target of the DML, not when any of the underlying tables are the target
2. in attempting to resolve your issue, the next logical thing you might think of would be to try to create your audit table on the underlying SYS objects that contribute to the ALL_TABLES view... however creating triggers on SYS objects is illegal:

create trigger nonono after insert on obj$

               *
ERROR at line 1:
ORA-04089: cannot create triggers on objects owned by SYS

which is why database event triggers were born.

++ mcs Received on Mon Mar 07 2005 - 17:41:53 CST