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!

Trigger to check for duplicates and update a value of an attribute

Discussion in 'SQL PL/SQL' started by alihht, Apr 18, 2013.

  1. alihht

    alihht Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    I have one table that has a code and index attributes.

    I'm trying to create a trigger that will check the values of code. If there is a duplicate in the inserted code, the index will be triggered to increase by 1. Example:

    code|c_index
    111 | 1
    112 | 1
    113 | 1
    111 | 2
    114 | 1
    112 | 2
    111 | 3

    This is my code, but it doesn't work:

    PHP:
    create trigger trg_update
    after insert on trial

    for each row

    declare v_index;

    begin

    select max(nvl(trial.c_index, 0)) into v_index
    from trial;

    if new.code = code then
    set new.c_index = v_index

    else
    set new.c_index = 1

    end if;
    end;
    ...............................

    I've tried to do a new better one, but still not working:

    PHP:
    create trigger trg_update
    after insert on trial
    for each row
    declare v_index number;

    begin

    if :new.code = :old.code then
    select max(nvl(c_index, 0)) into v_index
    from trial
    where code = :new.code;

    set :new.c_index = v_index + 1

    else
    set :new.c_index = 1

    end if;
    end;
     
    What's the problem with the codes above and what is the solution for the problem?

    ...............................................

    I tried another code:

    PHP:
    create trigger trg_update
    AFTER insert on trial
    for each ROW

    DECLARE v_index NUMBER := -1; -- "-1" is put in place so to be below the minimum value of the column
    DECLARE v_cnt NUMBER   := 0;

    BEGIN

    SELECT MAX(c_index), COUNT(*)
      INTO :v_index, v_cnt
      FROM trial
     WHERE code = :new.code;

    IF v_index <> -1 AND v_cnt > 1 THEN
    --Only one update here, for the newly inserted row explicitly
      UPDATE trial
         SET c_index = c_index +1
       WHERE code    = :new.code
         AND c_index = v_index
         AND ROWNUM  = 1;

    END IF;
    END;
     
    Some problems displayed:

    1- Error(2,1): PLS-00103: Encountered the symbol "DECLARE"
    2- Error(7,8): PLS-00049: bad bind variable 'V_INDEX'
    3- Error(9,15): PLS-00049: bad bind variable 'NEW.CODE'

    How can I solve these errors??
     
  2. alihht

    alihht Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    I solved the problem of the trigger and it's now compiled successfuly:

    PHP:
    CREATE OR REPLACE TRIGGER trg_update
    AFTER INSERT ON trial
    FOR EACH ROW

    DECLARE
    v_index NUMBER := -1; -- "-1" is put in place so to be below the minimum value of the column
    v_cnt NUMBER := 0;

    BEGIN

      SELECT MAX(c_index), COUNT(*)
        INTO v_index, v_cnt
        FROM trial
       WHERE code = :new.code;

      IF v_index <> -1 AND v_cnt > 1 THEN
      --Only one update here, for the newly inserted row explicitly
        UPDATE trial
           SET c_index = c_index +1
         WHERE code    = :new.code
           AND c_index = v_index
           AND ROWNUM  = 1;

      END IF;
    END;
    However, when I insert new value to the table it give me these errors:

    SQL Error: ORA-04091: table TRIAL1 is mutating, trigger/function may not see it
    ORA-06512: at "TRG_UPDATE", line 7
    ORA-04088: error during execution of trigger 'TRG_UPDATE'

    Any idea ??
     
  3. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    Hi,

    Yes, this exception was expected as u queried the same table you're inserting on. Actually, the mutating table exception is raised for the triggers fired FOR EACH ROW, as the trigger looses the trigger_record. Now, plz temme why u wanna have the c_index column? .. (purpose/how are u gonna use this column value later on) and how are the inserts done in the table? ... one record at a time or bulk insertion?
     
  4. alihht

    alihht Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hi, thanks for your reply :)

    c_index is to keep tracking the repetition of the code. The example in the first post is just a simple example of the big table that I have. In short words, I want to track the repetition of the code to record how many times it repeated in the code column.

    The records will be entered to the columns will be one at a time.
     
  5. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    Hi,

    So, if thats the purpose then why do u wanna have a separate column for that and filling up ur tablespace unnecessarily? ..
    U can count the redundant records while retrieving from the table via query.
     
  6. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Create a separate autonomous procedure for inserting or updating the table, with supplied parameters to procedure.

    And let us know the status here.....
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You are going about this the wrong way, you want a before insert trigger to check the input values BEFORE they get to the table:

    Code (SQL):
    CREATE TABLE trial(
            code NUMBER,
            c_index NUMBER
    );

    CREATE OR REPLACE TRIGGER trg_update
    BEFORE INSERT ON trial
    FOR EACH ROW
    DECLARE
    v_index NUMBER := 1;
    v_cnt NUMBER := 0;
    BEGIN
      SELECT MAX(c_index), COUNT(*)
        INTO v_index, v_cnt
        FROM trial
       WHERE code = :NEW.code;
      IF :NEW.c_index IS NULL AND v_cnt = 0 THEN
            :NEW.c_index:=1;
      ELSIF :NEW.c_index IS NULL AND v_cnt >= 1 THEN
            :NEW.c_index := v_index+1;
      END IF;
    END;
    /
     
    From this we get the desired result:

    Code (SQL):
    SQL> CREATE TABLE trial(
      2          code NUMBER,
      3          c_index NUMBER
      4  );
     
    TABLE created.
     
    SQL>
    SQL>
    SQL> CREATE OR REPLACE TRIGGER trg_update
      2  BEFORE INSERT ON trial
      3  FOR EACH ROW
      4
      5  DECLARE
      6  v_index NUMBER := 1;
      7  v_cnt NUMBER := 0;
      8
      9  BEGIN
     10
     11    SELECT MAX(c_index), COUNT(*)
     12      INTO v_index, v_cnt
     13      FROM trial
     14     WHERE code = :NEW.code;
     15
     16    IF :NEW.c_index IS NULL AND v_cnt = 0 THEN
     17          :NEW.c_index:=1;
     18    ELSIF :NEW.c_index IS NULL AND v_cnt >= 1 THEN
     19          :NEW.c_index := v_index+1;
     20    END IF;
     21  END;
     22  /
     
    TRIGGER created.
     
    SQL>
    SQL> INSERT INTO trial(code, c_index)
      2  VALUES(111,NULL);
     
    1 ROW created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> INSERT INTO trial(code, c_index)
      2  VALUES(111,NULL);
     
    1 ROW created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> INSERT INTO trial(code, c_index)
      2  VALUES(111,NULL);
     
    1 ROW created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> INSERT INTO trial(code, c_index)
      2  VALUES(111,NULL);
     
    1 ROW created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> INSERT INTO trial(code, c_index)
      2  VALUES(111,NULL);
     
    1 ROW created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> INSERT INTO trial(code, c_index)
      2  VALUES(111,NULL);
     
    1 ROW created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> INSERT INTO trial(code, c_index)
      2  VALUES(111,NULL);
     
    1 ROW created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> INSERT INTO trial(code, c_index)
      2  VALUES(111,NULL);
     
    1 ROW created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> INSERT INTO trial(code, c_index)
      2  VALUES(111,NULL);
     
    1 ROW created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> INSERT INTO trial(code, c_index)
      2  VALUES(111,NULL);
     
    1 ROW created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> INSERT INTO trial(code, c_index)
      2  VALUES(111,NULL);
     
    1 ROW created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> INSERT INTO trial(code, c_index)
      2  VALUES(111,NULL);
     
    1 ROW created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> INSERT INTO trial(code, c_index)
      2  VALUES(112,NULL);
     
    1 ROW created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> INSERT INTO trial(code, c_index)
      2  VALUES(113,NULL);
     
    1 ROW created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> INSERT INTO trial(code, c_index)
      2  VALUES(112,NULL);
     
    1 ROW created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> INSERT INTO trial(code, c_index)
      2  VALUES(113,NULL);
     
    1 ROW created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> INSERT INTO trial(code, c_index)
      2  VALUES(114,NULL);
     
    1 ROW created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> INSERT INTO trial(code, c_index)
      2  VALUES(114,NULL);
     
    1 ROW created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> INSERT INTO trial(code, c_index)
      2  VALUES(114,NULL);
     
    1 ROW created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> INSERT INTO trial(code, c_index)
      2  VALUES(114,NULL);
     
    1 ROW created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> INSERT INTO trial(code, c_index)
      2  VALUES(114,NULL);
     
    1 ROW created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> INSERT INTO trial(code, c_index)
      2  VALUES(115,NULL);
     
    1 ROW created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> INSERT INTO trial(code, c_index)
      2  VALUES(116,NULL);
     
    1 ROW created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> INSERT INTO trial(code, c_index)
      2  VALUES(117,NULL);
     
    1 ROW created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> SELECT * FROM trial;
     
          CODE    C_INDEX
    ---------- ----------
           111          1
           111          2
           111          3
           111          4
           111          5
           111          6
           111          7
           111          8
           111          9
           111         10
           111         11
     
          CODE    C_INDEX
    ---------- ----------
           111         12
           112          1
           113          1
           112          2
           113          2
           114          1
           114          2
           114          3
           114          4
           114          5
           115          1
     
          CODE    C_INDEX
    ---------- ----------
           116          1
           117          1
     
    24 ROWS selected.
     
    SQL>