12 Replies Latest reply on Aug 14, 2012 1:16 PM by CP

    How to Catch Unique Constraint in Exception

    CP
      Hi,

      I am writing a trigger TR_EMP on a table EMP which has columns EMP_ID, EMP_NAME, ALT_EMP_ID.

      Now I am updating ALT_EMP_ID for an EMP_ID(PK) which is unique.
      If ALT_EMP_ID is null
      then
            :new.ALT_EMP_ID = l_alt_emp_id;
      end if;
      As this ALT_EMP_ID is unique, same ID shouldn't be inserted again here. When data being inserted with 2 different sessions for 2 different EMP_ID there is a possible chance of inserting same ALT_EMP_ID for both which results in Unique error. I need to handle this exception. DUP_VAL_ON_INDEX or OTHERS Execption not able to handle this.

      Can you help me out on how to handle such exception?
        • 1. Re: How to Catch Unique Constraint in Exception
          jeneesh
          CP wrote:
          DUP_VAL_ON_INDEX or OTHERS Execption not able to handle this.

          Can you help me out on how to handle such exception?
          Could you elaborate this?

          I am able to use dup_val_on_index. Sample is shown below..(Just Sample!)
          begin
           insert into test values(1);
          exception
           when dup_val_on_index then
            insert into test values(2);
          end; 
          Moreover, what is your requirement? This design seems to be flawed. If you can explain teh actual requirement, forum members will be able to help you better..
          • 2. Re: How to Catch Unique Constraint in Exception
            Chanchal Wankhade
            You may require this:
            create or replace trigger trigger_name  before insert on emp 
            begin
            * *******
            *******
            *******
            exceptio
             when dup_val_on_index then
            dbms_output.put_line('you cannot insert duplicate value');
            when others then
            dbms_output.put_line('Please insert correct value');
            end;
            • 3. Re: How to Catch Unique Constraint in Exception
              CP
              >
              jeneesh wrote:
              I am able to use dup_val_on_index
              >

              Can you please write an Exception for this query?
              If ALT_EMP_ID is null
              then      
                    :new.ALT_EMP_ID = l_alt_emp_id;
              end if;
              jeneesh wrote:
              Moreover, what is your requirement? This design seems to be flawed. If you can explain teh actual requirement, forum members will be able to help you better..
              >

              Alternate Emp ID will be populated for Every employee. If on a same session when this ID is populated for 2 different Emplyoees then it will throw Unique constraint error, which should be handled.
              • 4. Re: How to Catch Unique Constraint in Exception
                266624
                Hi you have to put dup_val_on_index in inserting procedure in exception it may not useful.

                Since your problem in uniquness we may look from another perspective. I wonder how you get the value of l_alt_emp_id, may be using sequence help you with the uniqueness.

                Regards,
                • 5. Re: How to Catch Unique Constraint in Exception
                  Gurujothi
                  Hi CP,
                  As      svoktem suggested to avoid this kind of error ,it is better to have sequence rather creating Exception for the primary key column and assign it,Because suppose you are assiging one value if it already exist then you will get dup_val_on_index then how you know that what values are present and what values are not present?
                  Its better create one sequence and assign that sequence*(sequence_name.nextval)* to the Primary column,directly it will take the next value(sequence.nextval) then no need of Exception.

                  Edited by: Gurujothi on Aug 14, 2012 1:33 AM
                  • 6. Re: How to Catch Unique Constraint in Exception
                    CP
                    >
                    svoktem wrote:
                    I wonder how you get the value of l_alt_emp_id
                    >

                    There is a logic behind calculating this value. I am not using a Sequence. That's why I may end up with same l_alt_emp_id value for 2 Employees.
                    • 7. Re: How to Catch Unique Constraint in Exception
                      CP
                      Can't we handle this exception?

                      Exception raised in a trigger which is updating the record on which it is written during 2 concurrent updates with same value raising Unique Constraint.
                      • 8. Re: How to Catch Unique Constraint in Exception
                        Toon_Koppelaars-Oracle
                        There is a logic behind calculating this value. I am not using a Sequence. That's why I may end up with same l_alt_emp_id value for 2 Employees.
                        In that case you should put some required serialization code (using dbms_lock for instance) in your trigger, so that only one session at a time can execute this "logic".

                        You cannot catch the exception within your trigger-body code. It needs to be catched at the insert/update statement level, outside the trigger.
                        • 9. Re: How to Catch Unique Constraint in Exception
                          kendenny
                          Don't put your exception code in the trigger. The trigger isn't going to get the exception. The code that contains the "INSERT" statement is where the error needs to be handled.
                          • 10. Re: How to Catch Unique Constraint in Exception
                            266624
                            The problem is assigning the value in the trigger.
                            • 11. Re: How to Catch Unique Constraint in Exception
                              CP
                              >
                              kendenny wrote:
                              Don't put your exception code in the trigger. The trigger isn't going to get the exception. The code that contains the "INSERT" statement is where the error needs to be handled
                              >

                              No insert statement is present in the trigger. Just Updating 1 column in the table on which the trigger is written. Where should I capture the Exception for Updating the column?
                              • 12. Re: How to Catch Unique Constraint in Exception
                                CP
                                >
                                svoktem wrote:
                                The problem is assigning the value in the trigger.
                                >

                                That is the main logic for which the trigger is written. If I want to avoid that, I don't need the trigger. :(