1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Preventing duplicate column value using Trigger

Discussion in 'SQL PL/SQL' started by Tariq Bashir Malhi, Mar 8, 2010.

  1. Tariq Bashir Malhi

    Tariq Bashir Malhi Forum Advisor

    Messages:
    44
    Likes Received:
    0
    Trophy Points:
    80
    I want to avoid duplication on old_nic_no and new_nic_no number in a Table, for this i am using a following trigger

    Code (SQL):
    CREATE OR REPLACE TRIGGER chk_ip_duplications
       BEFORE UPDATE
       ON cb_insured_person_bak
       REFERENCING NEW AS NEW OLD AS OLD
       FOR EACH ROW
    DECLARE
       nic    VARCHAR2 (13);
       cnic   VARCHAR2 (15);
    BEGIN
       IF UPDATING AND :NEW.old_nic_no IS NOT NULL
       THEN
          SELECT   a.old_nic_no
            INTO   nic
            FROM   cb_insured_person_bak a
           WHERE   a.old_nic_no = :NEW.old_nic_no;

          IF SQL%FOUND
          THEN
             raise_application_error (
                -20009,
                'This NIC ' || :NEW.old_nic_no || ' Already Exist'
             );
          END IF;
       ELSIF UPDATING AND :NEW.new_nic_no IS NOT NULL
       THEN
          SELECT   a.new_nic_no
            INTO   cnic
            FROM   cb_insured_person_bak a
           WHERE   a.new_nic_no = :NEW.new_nic_no;

          IF SQL%FOUND
          THEN
             raise_application_error (
                -20009,
                'This CNIC ' || :NEW.new_nic_no || ' Already Exist'
             );
          END IF;
       END IF;
    EXCEPTION
       WHEN NO_DATA_FOUND
       THEN
          NULL;
    END;

    when i use following command

    Code (SQL):
    UPDATE cb_insured_person_bak SET new_nic_no = '34601-0774284-9' WHERE eobi_no = '4300G043746'
    34601-0774284-9 already exist with another eobi_no , i expect error described in rise_application_error clause but i give following error

    Code (SQL):
    ORA-04091: TABLE cb_insured_person_bak IS mutating..........
    How i can resolve this problem.....
    :hurray
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    One way would be to use "Instead of Trigger".

    Instead Of View

    But, If you want to avoid the duplication , Why cant you make these filed Unique ? Making these fields Unique is the Best method. Triggers will give always unexpected overhead in this case.
     
  3. Tariq Bashir Malhi

    Tariq Bashir Malhi Forum Advisor

    Messages:
    44
    Likes Received:
    0
    Trophy Points:
    80
    Thanks ........ I have made it U Key by adding constraint with NOVALIDATE option. One more thing we have date_of_birth column which is empty in some records, now we want to prevent it in further insertions, how can i do this.....
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Code (SQL):

    ALTER TABLE <tablename> MODIFY <columnname> NOT NULL enable novalidate;
     
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    The above method will work .. But may adversely affect the performance.

    So It is advised to give some default calu to the existing field . and Apply

    Code (SQL):

    ALTER TABLE <tablename> MODIFY <columnname> NOT NULL ;
     
     
  6. Tariq Bashir Malhi

    Tariq Bashir Malhi Forum Advisor

    Messages:
    44
    Likes Received:
    0
    Trophy Points:
    80
    Can you give me some details, i mean what type of adverse effects on performance would be.....
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Take the situation that you may need to export this table and import it into another database; the NOT NULL constraint as originally written would prevent the original records (those in the table before the NOT NULL was enforced) from loading and you would then lose data. Your best option is to alter the column to provide a default of, say, January 1, 4000, set the existing NULL dates to this value then alter the column to be NOT NULL. Doing this would automatically set the date to January 1, 4000 should a value not be entered:

    Code (SQL):
    ALTER TABLE <tablename> MODIFY <columnname> DEFAULT to_date('01/01/4000','mm/dd/yyyy');

    UPDATE <tablename> SET <columnname>  = to_date('01/01/4000','mm/dd/yyyy')
    WHERE <columnname> IS NULL;

    ALTER TABLE <tablename> MODIFY <columnname> NOT NULL;