Wednesday, June 24, 2015

Trigger to disable create objects in database starting with TMP or BAK

---- DISABLE TABLE NAMES STARTING WITH TMP OR BAK in Database ----

Intially :

create table tmp_test (fname varchar2(20));

After enabling below trigger, no more tables can be created in database starting with TMP or BAK:

create or replace TRIGGER NO_TMP_TABS_TRIG
BEFORE CREATE
ON DATABASE

DECLARE
 x user_tables.table_name%TYPE;
BEGIN
  SELECT ora_dict_obj_name
  INTO x
  FROM DUAL;

  IF SUBSTR(x, 0, 4) = 'TMP_' or SUBSTR(x, 0, 4) = 'BAK_' THEN
    RAISE_APPLICATION_ERROR(-20099, 'TABLE NAMES CAN NOT START WITH THE WITH TMP% OR BAK%');
  END IF;
END NO_TMP_TABS_TRIG;


Lets test it :

create table tmp_test (fname varchar2(20));


Error :

Error starting at line : 15 in command -
create table tmp_test (fname varchar2(20))
Error at Command Line : 15 Column : 1
Error report -
SQL Error: ORA-00604: error occurred at recursive SQL level 1
ORA-20099: TABLE NAMES CAN NOT START WITH THE WITH TMP% OR BAK%.

No comments:

Post a Comment