Restrictions on DDL-triggers in PL/SQL [message #666186] |
Wed, 18 October 2017 14:25 |
|
Alien
Messages: 292 Registered: June 1999
|
Senior Member |
|
|
Hi,
in the documentation https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/create_trigger.htm#BABIEBHC
it states
Quote:Restriction on Triggers on DDL Events
You cannot specify as a triggering event any DDL operation performed through a PL/SQL procedure.
What DDL operation from PL/SQL is meant here? I can only think of DDL in dynamic SQL, but could not reproduce the restriction:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> create procedure proc_test as
2 begin
3 execute immediate 'create table t1 (id number)';
4 end;
5 /
Procedure created.
SQL> create trigger trg_test before create on database
2 begin
3 raise_application_error(-20000,'Trigger fired');
4 end;
5 /
Trigger created.
SQL> create table t1 (id number);
create table t1 (id number)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: Trigger fired
ORA-06512: at line 2
SQL> exec proc_test;
BEGIN proc_test; END;
*
ERROR at line 1:
ORA-20000: Trigger fired
ORA-06512: at line 2
ORA-06512: at "SYSTEM.PROC_TEST", line 3
ORA-06512: at line 1
Any suggestion, on what I am missing?
Regards,
Arian
|
|
|
|
|
|