Re: Trigger at the Schema level - not working

"Jawahar Rajan" <jrajan_at_nc.rr.com> wrote in message news:<evGIb.236992$ea%.178270_at_news01.bloor.is.net.cable.rogers.com>...
> 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

You could keep this with only a minor change. Since your audit table name ends with '_A' just change this:

 IF (ORA_SYSEVENT = 'CREATE') Then
  If (ORA_DICT_OBJ_TYPE = 'TABLE') Then

to:

 IF (ORA_SYSEVENT = 'CREATE') AND
    (ORA_DICT_OBJ_TYPE = 'TABLE') AND
    (ora_dict_obj_Name NOT LIKE '%\_A' ESCAPE '\') THEN

To bypass creating an audit table for table names ending in '_A'.

Note: I like using 'AND' better than nested 'IF's if the second 'IF' is for no other purpose than to add additional conditions to the first. If you make this change you'll also have an extra 'END IF' which will need to be removed. Received on Fri Jan 02 2004 - 07:15:48 CST