Help me with my ddl monitor trigger

on Oracle 9204, I created the following trigger trying to catch the ddl sql.

create or replace trigger ddl_monitor_trigger

       after create or alter or drop on schema
       declare
           l_sysevent varchar2(25);
           l_status   varchar2(25);
           l_text varchar(1000);

       begin
          select sql_text into l_text from v$session vs,v$sql vsql
          where vs.audsid = (select SYS_CONTEXT('USERENV','SESSIONID')
 from dual)
          and vs.SQL_ADDRESS  = vsql.address
          and vs.SQL_HASH_VALUE = vsql.hash_value;
           select status into l_status from user_objects
where object_name = 'MY_PKG';
           if (l_status = 'INVALID' )
           then
                       insert into system.my_table
   (insert_time,sql_text)
                       values (sysdate,l_text);
          end if;
      end;

But when I issue the command "alter table drop partition x" what I catched is "select sql_text into l_text from v$session vs,v$sql vsql.....", not the one I wan trying to catch. I used prev_sql_addr and prev_hash_value, the result is the same, what I catched in the table is "select sql_text into l_text from v$session vs,v$sql vsql.....".

Can somebody tell me what went wrong?

Thanks

Joe Received on Thu Feb 19 2004 - 06:26:52 CST