#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    6
    Rep Power
    0

    Creating a trigger within if-else statement


    Yet another noob question:

    I would like to create a trigger on a table, but only if the table exists.

    This is what i've got so far:

    PHP Code:
    create or replace function tableExists (tablename in varchar2) return boolean
    is
      c int

    begin    
      select count
    (*) into c from user_tables where table_name upper(table_name);    
      if 
    1 then       
        
    return true;      
      
    end if; 
      return 
    false;
    end;
    /    

    begin   
      
    if tableExists('USER_GROUP'then
      create 
    or replace trigger Delete_UserGroup_tr
      after update of HANDSHAKE on USER_GROUP
      begin
        delete USER_GROUP WHERE HANDSHAKE 
    'd';
      
    end;
      
    end if;
    end;

    This would give me the error:

    PLS-00103: Encountered the symbol "CREATE" when expecting one of the following: ( begin case declare exit for goto if loop mod null pragma raise return select update

    Please some advice
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    6
    Rep Power
    0
    One step futher no:

    PHP Code:
    begin   
      
    if tableExists('USER_GROUP'then
      execute immediate 
    'create or replace trigger Delete_UserGroup_tr
      after update of HANDSHAKE on USER_GROUP
      begin
        delete USER_GROUP WHERE HANDSHAKE = "d";
      end;'
    ;
      
    end if;
    end
    The script runs, says '1 row updated', but the trigger does not run, it's not in the overview of all triggers in the database.
    Does it mean it is only local within if-ele statement?
    If so, how to make it global?

IMN logo majestic logo threadwatch logo seochat tools logo