Re: DDL trigger only working on one database

Eric Kimble <ekimble_at_wfl.fha.dot.gov> wrote in message news:<3DBF0D45.C420A7E3_at_wfl.fha.dot.gov>...
> Hello, I was trying to implement the ddl trigger exercise located on
> Jonathan's page written by Norman (thanks for all you folks have done, I
> really appreciate it) on one instance. It didn't work (no errors so I
> tried it on another and it worked. I don't get any error just no data.
> EKIMBLE_at_DOLPHIN>alter table test nologging;
>
> Table altered.
>
> EKIMBLE_at_DOLPHIN>alter table test logging;
>
> Table altered.
>
> EKIMBLE_at_DOLPHIN>select * from logging.ddl_log;
>
> no rows selected
>
>
> between the 2 machines I used the same script and only changed the
> location of the datafile for the tablespace. They are both 8.1.7.4
> running on nt and 2k. The one on NT works the one on 2000 didn't. I took
> out the exceptions clause and tried it that way to see if it was created
> errors and it didn't change anything. Here is a copy of the script that
> I used.
>
> create tablespace logging
> datafile 'c:\oracle\oradata\dolphin\logging01.dbf' size 200M
> extent management local
> uniform size 64K;
>
> create user logging identified by logging
> default tablespace logging
> temporary tablespace temp
> quota 0 on SYSTEM
> quota 200M on LOGGING;
>
> create table logging.ddl_log
> ( user_name varchar2(30),
> ddl_date date,
> ddl_type varchar2(30),
> object_type varchar2(18),
> owner varchar2(30),
> object_name varchar2(128)
> ) tablespace logging;
>
> --Create the trigger -
> create or replace trigger DDLTrigger
> after DDL on database
> begin
> insert into logging.ddl_log
> values (ora_login_user, sysdate, ora_sysevent, ora_dict_obj_type,
> ora_dict_obj_owner, ora_dict_obj_name);
> exception
> when others then
> NULL;
> end;
> /

Eric, since we know the code is good (as it worked on one instance) then I would think that means it is a db specific issue. Check the init.ora and registry settings for _SYSTEM_TRIG_ENABLED = FALSE. This parameter disables db event triggers and has to be set during upgrades from prior releases so it seems a likely possiblility that it is set.

HTH -- Mark D Powell -- Received on Wed Oct 30 2002 - 07:49:01 CST