Home » SQL & PL/SQL » SQL & PL/SQL » DDL Trigger not firing -- Why??
DDL Trigger not firing -- Why?? [message #19803] Wed, 10 April 2002 12:43 Go to next message
Kutsal Berberoglu
Messages: 3
Registered: April 2002
Junior Member
Hi folks..

I'm running Oracle 8.1.7 on Solaris. I've created a DDL trigger in a schema, but that trigger is not firing at all. This is the trigger:
CREATE OR REPLACE TRIGGER test 
BEFORE DDL ON SCHEMA
BEGIN
  raise_application_error(-20202, 'Yeah, right..');
END;

According to this, any subsequent DDL statement on this Schema should fail with that exception.. But I can happily go ahead and create/drop/alter any table in the schema without any error messages....

What do I need to look for? Why would this happen?

Thanks!!

-Kutsal
Re: DDL Trigger not firing -- Why?? [message #19804 is a reply to message #19803] Wed, 10 April 2002 12:47 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10626
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
works fine for me!!!
SQL> ed
Wrote file afiedt.buf

1 CREATE OR REPLACE TRIGGER test BEFORE DDL ON SCHEMA
2* BEGIN raise_application_error(-20202, 'Yeah, right..');END;
3 /

Trigger created.

SQL> create table po
2 (id number);
create table po
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20202: Yeah, right..
ORA-06512: at line 1

SQL> drop table emp;
drop table emp
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20202: Yeah, right..
ORA-06512: at line 1
Re: DDL Trigger not firing -- Why?? [message #19807 is a reply to message #19803] Wed, 10 April 2002 13:28 Go to previous message
Kutsal Berberoglu
Messages: 3
Registered: April 2002
Junior Member
Here's what happens when I do it:
SQL> CREATE OR REPLACE TRIGGER test BEFORE DDL ON SCHEMA BEGIN raise_application_error(-20202, 'Yeah, right..'); END;

Trigger created.

SQL> CREATE TABLE foo ( id number );

Table created.

SQL> DROP TABLE foo;

Table dropped.


And I cannot figure out why the heck that trigger is not firing.... It's going to drive me insane...

-Kutsal
Previous Topic: three maximum salary from each department?
Next Topic: Optional parameters in oracle procs
Goto Forum:
  


Current Time: Sat Sep 20 09:12:29 CDT 2014

Total time taken to generate the page: 0.07178 seconds