如果“後”DDL 觸發器導致錯誤，DDL 是否回滾？

有人建議DDL 在邏輯上執行如下：

begin
   COMMIT;
   perform any appropriate pre-DDL trigger code;
   do the ddl;
   perform any appropriate post-DDL trigger code;
   COMMIT;
exception
   when others then
        ROLLBACK;
        raise;
end;

這表明觸發器中的任何錯誤都會導致 DDL 回滾。是這樣嗎？

至少在 11.2 中，答案是“視情況而定”：

這create是回滾的：

create trigger trig_foo after create on schema
begin
 raise_application_error(-20001, 'Dont do it!');
end;
/
--
create table foo as select level as id from dual connect by level<=10000;
/*
SQL Error: ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Dont do it!
*/
--
select count(*) from foo;
/*
SQL Error: ORA-00942: table or view does not exist
*/

但這truncate不是：

create table foo as select level as id from dual connect by level<=10000;
--
create trigger trig_foo after truncate on schema
begin
 raise_application_error(-20001, 'Dont do it!');
end;
/
--
truncate table foo;
/*
SQL Error: ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Dont do it!
*/
select count(*) from foo;
/*
COUNT(*)               
---------------------- 
0                  
*/

引用自：https://dba.stackexchange.com/questions/9700