trigger on a creation of a view- how to write it - Oracle

Question

I don't know how to write a trigger in Oracle when the event is the creation of a view...
1st) is it possible?
2nd) how can I write it? Something like this?

create or replace trigger Creat_View
after create view <name of the view>

I'm at my first triggers so I'm sorry if I make huge mistakes!

M

Answer 1

You'd use a schema level trigger for that (assuming your user is named SCOTT):

CREATE OR REPLACE TRIGGER no_create_view_trg 
  BEFORE CREATE ON SCOTT.SCHEMA 
DECLARE
   v_msg VARCHAR2(1000) :=
      'No create view allowed for CUSTOM on ' ||
       DICTIONARY_OBJ_OWNER || '.' ||
       DICTIONARY_OBJ_NAME || ' from ' ||
       LOGIN_USER;  
BEGIN
  IF DICTIONARY_OBJ_OWNER = user AND
     DICTIONARY_OBJ_NAME LIKE 'CUSTOM%' AND
     DICTIONARY_OBJ_TYPE = 'VIEW'
  THEN
     RAISE_APPLICATION_ERROR (
        -20905, v_msg);
  END IF;
END;

This will prevent the creation of views named CUSTOM_..., but allow creation of all other views.