I thought that general framework for DDL triggers defined AFTER ALTER was like this:
-- implicit COMMIT before DDL statement (here: ALTER TABLE)
-- DDL statement
-- implicit COMMIT after DDL statement (here: ALTER TABLE)
-- AFTER ALTER DDL trigger fires
And I also thought that everything is inside that trigger would see "new" DDL code as introduced by ALTER TABLE statement. But it looks like I miss something about this situation because the following trigger fires dbms_metadata.get_ddl procedure and sees "old" DDL code (before ALTER TABLE statement).
What do I miss? Could you give me some hints about this behavior? If I shouldn't expect returned DDL to be "current" then why I shouldn't?
Thank you
/* all done in SCOTT schema */
create table y ( col1 varchar2(5) );
create table t ( dt date default sysdate, d varchar2(4000) );
create or replace trigger trg
after ALTER ON scott.SCHEMA
declare
v_d varchar2(4000);
begin
select cast(dbms_metadata.get_ddl('TABLE', sys.dictionary_obj_name, sys.dictionary_obj_owner) as varchar2(4000))
into v_d
from dual;
insert into t(d) values (v_d);
end;
alter table y add col2 int;
select * from t; -- here is "old" DDL with col1 only