Home » SQL & PL/SQL » SQL & PL/SQL » Restrictions on DDL-triggers in PL/SQL (Oracle XE 11.2.0.2 on Windows 10)
Restrictions on DDL-triggers in PL/SQL [message #666186] Wed, 18 October 2017 14:25 Go to next message
Alien
Messages: 274
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
Re: Restrictions on DDL-triggers in PL/SQL [message #666193 is a reply to message #666186] Thu, 19 October 2017 02:12 Go to previous messageGo to next message
Michel Cadot
Messages: 65201
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Maybe a released restriction as this information no more exists in 11.2, 12.1 and 12.2 PL/SQL or SQL Reference books (your link points to a 11.1 book).

[Updated on: Thu, 19 October 2017 02:18]

Report message to a moderator

Re: Restrictions on DDL-triggers in PL/SQL [message #666194 is a reply to message #666193] Thu, 19 October 2017 02:18 Go to previous messageGo to next message
Michel Cadot
Messages: 65201
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Or maybe a document bug as I tried your test case in 10.2.0.5, 9.2.0.8 and 8.1.7.4, it gives the same thing.

Re: Restrictions on DDL-triggers in PL/SQL [message #666198 is a reply to message #666194] Thu, 19 October 2017 08:06 Go to previous message
Alien
Messages: 274
Registered: June 1999
Senior Member
Thanks Michel,

I indeed had the wrong book. But thanks for testing in the old versions too! At least I don't have to worry about that restriction then.

Regards,

Arian
Previous Topic: DBTime Zone&Off Set
Next Topic: REPLACE Function
Goto Forum:
  


Current Time: Tue Nov 21 04:21:25 CST 2017

Total time taken to generate the page: 0.07295 seconds