daily pastebin goal
5%
SHARE
TWEET

Untitled

a guest May 17th, 2018 112 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. create or replace trigger creator_trigger
  2. after create on schema
  3. begin
  4.     execute immediate 'create or replace trigger ' || ora_dict_obj_name || '_uniq_trigger ' ||
  5.         'before insert or update on ' || ora_dict_obj_name || ' ' ||
  6.         'for each row' || ' ' ||
  7.         'declare
  8.            TYPE column_ref_cursor IS REF CURSOR;
  9.            col_cur column_ref_cursor;
  10.            col_rec varchar2(100);
  11.            t_name varchar2(250) := ' || ora_dict_obj_name || '; ' ||
  12.            'column_statement varchar2(250) := ''select column_name from all_tab_columns where owner=''STABOL'' and table_name = '' || chr(39) || t_name || chr(39);
  13.        
  14.            inserted_val varchar2(500);
  15.        
  16.            can_insert boolean := true;
  17.            cnt number(4) := 0;
  18.            query_statement varchar2(300);
  19.         begin
  20.             open col_cur for column_statement;
  21.             loop
  22.                 fetch col_cur into col_rec;
  23.                 exit when col_cur%NOTFOUND;
  24.                
  25.                 execute immediate ''inserted_val := :NEW.'' || col_req;    
  26.                 query_statement := ''select count(*) from '' || '' stabol.'' || t_name ||
  27.                     '' where '' || col_rec || '' = '' || chr(39) || inserted_val || chr(39);  
  28.                 execute immediate query_statement into cnt;
  29.                
  30.                 if cnt != 0 then
  31.                     can_insert := false;
  32.                     exit;
  33.                 end if;
  34.             end loop;
  35.             CLOSE col_cur;
  36.            
  37.             if can_insert = false then
  38.                  raise_application_error (-20000,''row already exists'');
  39.             end if;
  40.         end;';
  41. end;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top