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 18, 2010.

  1. Tariq Bashir Malhi

    Tariq Bashir Malhi Forum Advisor

    Messages:
    44
    Likes Received:
    0
    Trophy Points:
    80
    I am working on a assignment, How to prevent duplication with Trigger. I create following compound trigger for this, but it is not doing so. How i can improve this to get required result......
    I know this can be done with Constraint and ........
    But this is a assignment , please suggest solution through trigger.

    Code (SQL):
    CREATE OR REPLACE TRIGGER CHK_DUPLICATION FOR
       INSERT
       ON TST
       COMPOUND TRIGGER
       R_BCOUNT   NUMBER;

       R_ACOUNT   NUMBER;

       D_VAL      NUMBER;
       BEFORE STATEMENT
       IS
       BEGIN
          R_BCOUNT := 0;



          SELECT   COUNT ( * ) INTO R_BCOUNT FROM TST;
       END
       BEFORE STATEMENT;

       BEFORE EACH ROW
       IS
       BEGIN
          D_VAL := 0;

          SELECT   COUNT ( * )
            INTO   D_VAL
            FROM   TST
           WHERE   :NEW.C1 = :OLD.C1;

          IF D_VAL > 0
          THEN
             RAISE_APPLICATION_ERROR (-20010, 'Duplicate C1 Value ' || :NEW.C1);
          END IF;
       END
       BEFORE EACH ROW;

       AFTER EACH ROW
       IS
       BEGIN
          IF D_VAL > 0
          THEN
             RAISE_APPLICATION_ERROR (-20010, 'Duplicate C1 Value ' || :NEW.C1);
          END IF;
       END
       AFTER EACH ROW;

       AFTER STATEMENT
       IS
       BEGIN
          SELECT   COUNT ( * ) INTO R_ACOUNT FROM TST;

          DBMS_OUTPUT.PUT_LINE ('Rec Count before insert ' || R_BCOUNT);

          DBMS_OUTPUT.PUT_LINE ('Rec Count After insert ' || R_ACOUNT);
       END
       AFTER STATEMENT;

    END CHK_DUPLICATION;
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Are you trying to prevent Duplicate insert? orr Update ?

    Anyway please not that :New and :eek:ld will work only for the single record. So change code like ,


    Code (SQL):
      SELECT COUNT(*)
        INTO L_cnt  
        FROM tab
       WHERE col= :NEW.col;
    This will work with Insert .. But will fail on Update. For update u might have to use instead of view trigger.
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Your trigger isn't working because your AFTER ROW code is wrong; you WANT the count to be > 0 (that would be 1) but you do not want it to be greater than 1 ( that would indicate duplicates). You'll want your code to look like this:

    Code (SQL):
    CREATE OR REPLACE TRIGGER CHK_DUPLICATION FOR
       INSERT
       ON TST
       COMPOUND TRIGGER
       R_BCOUNT   NUMBER;

       R_ACOUNT   NUMBER;

       D_VAL      NUMBER;
       BEFORE STATEMENT
       IS
       BEGIN
          R_BCOUNT := 0;

          SELECT   COUNT ( * ) INTO R_BCOUNT FROM TST;
       END
       BEFORE STATEMENT;

       BEFORE EACH ROW
       IS
       BEGIN
          D_VAL := 0;

          SELECT   COUNT ( * )
            INTO   D_VAL
            FROM   TST
           WHERE   :NEW.C1 = :OLD.C1;

          IF D_VAL > 0
          THEN
             RAISE_APPLICATION_ERROR (-20010, 'Duplicate C1 Value ' || :NEW.C1);
          END IF;
       END
       BEFORE EACH ROW;

       AFTER EACH ROW
       IS
       BEGIN
          IF D_VAL > 1
          THEN
             RAISE_APPLICATION_ERROR (-20010, 'Duplicate C1 Value ' || :NEW.C1);
          END IF;
       END
       AFTER EACH ROW;

       AFTER STATEMENT
       IS
       BEGIN
          SELECT   COUNT ( * ) INTO R_ACOUNT FROM TST;

          DBMS_OUTPUT.PUT_LINE ('Rec Count before insert ' || R_BCOUNT);

          DBMS_OUTPUT.PUT_LINE ('Rec Count After insert ' || R_ACOUNT);
       END
       AFTER STATEMENT;

    END CHK_DUPLICATION;
    This will ensure the record you just inserted is not considered a duplicate and therefore will not throw an error. There may be other reasons why your code isn't doing what you expect; you need to post an example of trying to insert a duplicate record using this trigger and show us the results.