Home » SQL & PL/SQL » SQL & PL/SQL » aborting DDL within trigger (Triggers)
aborting DDL within trigger [message #301404] Wed, 20 February 2008 08:24 Go to next message
sammeras
Messages: 28
Registered: September 2007
Location: Israel
Junior Member

Hello,
I'm tring to cancel ALTERING table on schema using Trigger.

-- Sample table
CREATE TABLE s2(id NUMBER); 


-- Create trigger
CREATE OR REPLACE TRIGGER roy.trig1
	BEFORE ALTER OR DROP ON SCHEMA
DECLARE
	v_sysevent VARCHAR2(120);
	v_obj_type  VARCHAR2(120);
	v_obj_name VARCHAR2(120);
	v_user VARCHAR2(30);
	--PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
	SELECT ora_sysevent, ora_dict_obj_type, ora_dict_obj_name, user 
	INTO v_sysevent, v_obj_type, v_obj_name, v_user  FROM dual;
	
	IF (v_sysevent = 'ALTER') AND (v_obj_type = 'TABLE') THEN 
		ROLLBACK;			
		dbms_output.put_line('DDL Canceld !');
	END IF;	
END trig1;
/
sho err


When i run alter:
-- Alter table s2
ALTER TABLE s2 ADD (name VARCHAR2(30));


I got errors:
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-04092: cannot ROLLBACK in a trigger
ORA-06512: at line 12


I know that i can't use ROLLBACK or COMMIT within ddl trigger.

Can anybody help me to cancel the adding column to the s2 table?
Is there another way to da that without errors?

Thanks guys.
SAM


Re: aborting DDL within trigger [message #301409 is a reply to message #301404] Wed, 20 February 2008 08:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
RAISE_APPLICATION_ERROR.

Regards
Michel
Re: aborting DDL within trigger [message #301750 is a reply to message #301409] Thu, 21 February 2008 09:19 Go to previous message
sammeras
Messages: 28
Registered: September 2007
Location: Israel
Junior Member

Thanks
Previous Topic: Materialized view ......subquery expression not allowed error
Next Topic: How to refresh session in Sql Plus or Toad
Goto Forum:
  


Current Time: Fri Dec 02 14:05:11 CST 2016

Total time taken to generate the page: 0.09655 seconds