CODEWITHSUNDEEP
sqloracle-databaseplsql
Eclipsa2
Eclipsa2

Reputation: 23

DDL trigger not working when trying to drop table

I am trying to create a simple DDL trigger that won't let me drop any table inside my database

The trigger creates but I get an error when I try to test the trigger if it is working or not:

`CREATE OR REPLACE TRIGGER Exercitiul12
BEFORE DROP ON SCHEMA
BEGIN
    RAISE_APPLICATION_ERROR(-20001, 'Nu puteti sterge tabelul ' || ora_dict_obj_name);
END;
/

BEGIN
    DROP TABLE Restaurant;
END;
/`

How can I solve the problem?

Upvotes: 0

Views: 66

Answers (1)

Beefstu
Beefstu

Reputation: 857

You can try something like this:

create or replace trigger delete_disabling_trigger
  before drop on database
begin
    if(ORA_DICT_OBJ_NAME = 'MY_TABLE') then --and ORA_DICT_OBJ_OWNER = 'YOUR_SCHEMA'  
    dbms_output.put_line('delete_disabling_trigger');
    RAISE_APPLICATION_ERROR(-20000,'Cant delete this table');
    end if;
end;
/

Upvotes: 1

Related Questions