Thanks for posting this, I have a few questions. Why you need to look at the SQL statement at all. You already know that the statement is ALTER TABLE, so why not just check for dropped columns. if ora_dict_obj_type = 'TABLE' THEN if ora_dict_obj_type = 'ALTER' THEN for x in get_column_name loop if ora_is_drop_column (x.column_name) then object_security.record_destructive_ddl (x.column_name); end if; end loop; else ... Is it a performance thing? Is it faster to retrieve the SQL all the time and only check for dropped columns when you have to? And Is that also the reason for excluding the SYS schema? chaim "MacGregor, Ian A." <[EMAIL PROTECTED]> on 06/11/2001 07:18:21 PM Please respond to [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: (bcc: Chaim Katz/Completions/Bombardier) CREATE or replace TRIGGER record_destructive_ddl_trigger before drop or alter or truncate ON DATABASE Declare pcolumn_name dba_tab_columns.column_name%type; cursor get_column_name is select column_name from dba_tab_columns where owner = ora_dict_obj_owner and table_name = ora_dict_obj_name; sql_dummy varchar2(1); cursor is_drop_column is select 'x' from sys.x$_kglpn p, sys.x$_kglcursor c, v$session s where p.kglpnhdl = c.kglhdadr and p.kglpnses = s.saddr and instr(lower(replace(c.kglnaobj,' ', null)),'select') = 0 and instr(lower(replace(c.kglnaobj,' ', null)),'dropcolumn') > 0 and s.audsid = sys_context('USERENV', 'SESSIONID'); Begin if (((ora_sysevent = 'DROP') and (ora_dict_obj_type = 'TABLE')) or ora_sysevent = 'TRUNCATE') and ora_dict_obj_owner != 'SYS' THEN object_security.record_destructive_ddl(null); elsif ora_sysevent = 'ALTER' and ora_dict_obj_type = 'TABLE' and ora_dict_obj_owner != 'SYS' THEN open is_drop_column; fetch is_drop_column into sql_dummy; close is_drop_column; if sql_dummy = 'x' then open get_column_name; loop fetch get_column_name into pcolumn_name; exit when get_column_name%notfound; if ora_is_drop_column(pcolumn_name) then object_security.record_destructive_ddl(pcolumn_name); end if; end loop; end if; close get_column_name; end if; end; / -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).