1 2 Previous Next 16 Replies Latest reply: Apr 4, 2013 9:53 AM by VinayMummadi RSS

    Create a trigger to check if synonym already exists in db

    VinayMummadi
      Hello,
      How can I create a trigger to check if synonym already exists in db and if exists then don't create synonym.

      my work: ( this is just like an outline i prepared)

      select * from all_synonyms;
      declare
      s_exists number;
      begin
      -- check whether the synonym exists
      select 1 into s_exists from all_synonyms;
      -- an error gets raise if it doesn't
      exception when no_data_found then
      -- DDL has to be done inside
      execute immediate ' create or replace synonym';
      end;
      /

      any help is really appreciated..

      thanks,

      Edited by: Vinay Mummadi on Mar 29, 2013 12:51 PM
        • 1. Re: Create a trigger to check if synonym already exists in db
          Hoek
          A trigger or a PL/SQL block?
          Explain your requirement in more detail.
          Anyway, there are System Triggers that fire on DDL:
          http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/triggers.htm#LNPLS99887
          You might find it an interesting read...
          • 2. Re: Create a trigger to check if synonym already exists in db
            sb92075
            Vinay Mummadi wrote:
            Hello,
            How can I create a trigger to check if synonym already exists in db and if exists then don't create synonym.
            TRIGGER?
            my work: ( this is just like an outline i prepared)

            select * from all_synonyms;
            declare
            s_exists number;
            begin
            -- check whether the synonym exists
            select 1 into s_exists from all_synonyms;
            -- an error gets raise if it doesn't
            exception when no_data_found then
            -- DDL has to be done inside
            execute immediate ' create or replace synonym';
            end;
            /

            any help is really appreciated..

            thanks,

            Edited by: Vinay Mummadi on Mar 29, 2013 12:51 PM
            why do you need to check prior to
            CREATE OR REPLACE SYNONYM?

            Application objects should only ever change during Application version upgrades & should be done via static SQL & not by PL/SQL!
            • 3. Re: Create a trigger to check if synonym already exists in db
              Peter Gjelstrup
              Hi Vinay,

              Why a trigger? - Which event should fire it?

              Why not simply use Create (or Replace) synonym .... straight off

              Do you really care if it is there, already?

              Regards
              Peter
              • 4. Re: Create a trigger to check if synonym already exists in db
                APC
                Vinay Mummadi wrote:
                How can I create a trigger to check if synonym already exists in db and if exists then don't create synonym.
                The correct solution is to just use CREATE SYNONYM : that will fail if the synonym already exists.

                The other correct solution is to use a decent source control and configuration management system, so that you know whether a synonym exists before you run a script.

                As the Art of War says, if you have to resort to triggers you've already lost.

                Cheers, APC
                • 5. Re: Create a trigger to check if synonym already exists in db
                  VinayMummadi
                  @hoek
                  sorry, i meant a block which should not allow(fire) create or replace synonym, if the synonym already exists in the DB (with same name).
                  • 6. Re: Create a trigger to check if synonym already exists in db
                    VinayMummadi
                    @peter-
                    it should fire on replace, i can create a trigger on 'before create synonym' but this will restrict users from creating new synonym.

                    my objective is not to overwrite or replace an existing synonym, something saying can't overwrite or replace or synonym already exists..

                    Edited by: Vinay Mummadi on Mar 29, 2013 1:16 PM
                    • 7. Re: Create a trigger to check if synonym already exists in db
                      Peter Gjelstrup
                      my objective is not to overwrite or replace an existing synonym, something saying can't overwrite or replace or synonym already exists..
                      Vinay, that's pretty straight forward:
                      SQL> create synonym your_synonym for your_object;
                      create synonym your_synonym for your_object
                                     *
                      ERROR at line 1:
                      ORA-00955: name is already used by an existing object
                      
                      SQL>
                      Regards
                      Peter
                      • 8. Re: Create a trigger to check if synonym already exists in db
                        VinayMummadi
                        ty Peter,but
                        what if the user says 'create or replace synonym' ? - here it will replace the existing synonym

                        if someone(users) does the above stmt., some code/proc/block should check prior for the synonym to see if exists or not?- if it exists then throw an error/exception saying already exists,

                        if not, then create the synonym.

                        Ty,
                        • 9. Re: Create a trigger to check if synonym already exists in db
                          Peter Gjelstrup
                          Vinay Mummadi wrote:
                          what if the user says 'create or replace synonym' ? - here it will replace the existing synonym
                          Yes, it will. But what are "users"? - Ordinary users do not create synonyms. And if they do they do it in their own schema.
                          if someone(users) does the above stmt., some code/proc/block should check prior for the synonym to see if exists or not?- if it exists then throw an error/exception saying already exists,
                          if not, then create the synonym.
                          That is exactly what I demonstrated, create an already existing synonym and an exception was raised saying that. (Or more precisely, that an existing object already has that name)

                          Sorry, but I do not understand the problem you are trying to solve. Did you have an incident where "someone" issued a Create Or Replace synonym, where he/she should not have?

                          BR
                          Peter
                          • 10. Re: Create a trigger to check if synonym already exists in db
                            VinayMummadi
                            ty Peter,
                            yes, okay here in my case users(like developers,testers) should not overwrite or modify existing synonyms on complete DB. expect DBA's or whoever has DBA role , but they(users) may create new synonyms.

                            ex:
                            create public synonym test_vmummadi for vmummadi.ddl_log;

                            ORA-00955: name is already used by an existing object

                            create or replace public synonym test_vmummadi for vmummadi.ddl_log;

                            Synonym created.

                            yes, there was an existing synonym on DB which was for some purpose and someone else modified that synonym according to their requirement. This triggered some jobs to fail and interrupted the flow of the first job.

                            so we came up with this - users can create synonyms(new) but should not be able to modify/replace the existing ones. If they try to do something like this it should not allow them(throw exception/error)
                            • 11. Re: Create a trigger to check if synonym already exists in db
                              Peter Gjelstrup
                              Thanks for explaining Vinay,

                              Maybe something like this then:
                              create or replace trigger synonym_trigger
                                before create on scott.schema
                              declare
                                function syn_exists
                                  return boolean is
                                  v_dummy   varchar2 (1);
                                begin
                                  select null
                                  into   dummy
                                  from   all_synonyms
                                  where      owner = ora_dict_obj_owner
                                         and synonym_name = ora_dict_obj_name;
                              
                                  return true;
                                exception
                                  when no_data_found then
                                    return false;
                                end syn_exists;
                              begin
                                if ora_dict_obj_type = 'SYNONYM' then
                                  if syn_exists then
                                    raise_appliation_error ( -20000, 'Synonym ' || ora_dict_obj_name || ' aready exists');
                                  end if;
                                end if;
                              end;
                              /
                              It is just an idea, you will have to finish it yourself.

                              Regards
                              Peter
                              • 12. Re: Create a trigger to check if synonym already exists in db
                                VinayMummadi
                                ty very much Peter, appreciate ur efforts-

                                sure, i will take on from here and see what I can modify to my needs.

                                -Vinay
                                • 13. Re: Create a trigger to check if synonym already exists in db
                                  Peter Gjelstrup
                                  Hello again,

                                  Slightly different version, allowing for DBA role to by-pass the check:
                                  create or replace trigger synonym_trigger
                                    before create on scott.schema
                                  declare
                                    function syn_exists
                                      return boolean is
                                      v_dummy   varchar2 (1);
                                    begin
                                      select null
                                      into   dummy
                                      from   all_synonyms
                                      where      owner = ora_dict_obj_owner
                                             and synonym_name = ora_dict_obj_name;
                                  
                                      return true;
                                    exception
                                      when no_data_found then
                                        return false;
                                    end syn_exists;
                                  begin
                                    if ora_dict_obj_type = 'SYNONYM' then
                                      if syn_exists then
                                        if not dbms_session.is_role_enabled ('DBA') then
                                          raise_appliation_error ( -20000, 'Synonym ' || ora_dict_obj_name || ' aready exists');
                                        end if;
                                      end if;
                                    end if;
                                  end;
                                  /
                                  Regards
                                  Peter
                                  • 14. Re: Create a trigger to check if synonym already exists in db
                                    VinayMummadi
                                    :) thanks again, will try incl this
                                    1 2 Previous Next