5

It has been suggested that DDL is logically performed something like this:

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;

Which would suggest that any error in a trigger would cause the DDL to be rolled back. Is this the case?

9

The answer, at least on 11.2, is "It depends":

This create is rolled back:

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
*/

But this truncate is not:

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                  
*/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.