In the trigger I posted I closed the "get_column_name" cursor at the wrong spot. It
worked beautifully for "drops", but
"adds" complained
Here is the corrected code
------------------------------------------------------------------------------------------------------------------
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)),'dropcolumn') > 0 and
instr(lower(c.kglnaobj),'select') = 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;
close get_column_name;
end if;
end if;
end;
/
--------------------------------------------------------------------------------------------------------
Warning I'm still testing this.
As far as wanting to know whether drop column was issued, I don't want to loop
through all the columns in the table
when a constraint is created, or a column is added, or a column is widened etc. Yes,
my thinking is that its better to check the SQL rather than calling the function
needlessly. But I have not done any performance testing.
I cannot think of any DDL which is issued against the sys owned tables after database
create time, nor a reason for there to be. At one time tables needed to support
certain database options were owned by sys, but I don't think that's true anymore; I
haven't installed every option. If these premises are true, it is better to stop any
DDL against a sys table than to record it, and that's a different trigger. I will
probably remove the exclusion on sys tables from the present trigger to see if any DDL
against them is trapped before writing a trigger which prevents such DDL.
Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]
Original Message-----
[mailto:[EMAIL PROTECTED]]
Sent: Tuesday, June 12, 2001 1:31 PM
To: Multiple recipients of list ORACLE-L
Column"
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: MacGregor, Ian A.
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).
