0

I'm trying to implement a dedupe trigger to a table.

CREATE OR REPLACE TRIGGER USER.TBL_ACTION_DEDUP
BEFORE INSERT
ON USER.ERRORTABLE 
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN  
   IF (:OLD.Identifier = :NEW.Identifier) THEN  
      SELECT :OLD.tally + 1 INTO :NEW.tally FROM DUAL;
   END IF;  
END TBL_ACTION_DEDUP;
/

Error is: On line: 2 PLS-00049: bad bind variable 'OLD.IDENTIFIER'

4
  • 1
    What is the old value of IDENTIFIER and TALLY if you insert a new row? Old values only exist for update and delete triggers.
    – miracle173
    Commented Apr 6, 2014 at 6:44
  • Why are you using a select ... from dual to assign the new value? Much better just to do :new.tally = :old.tally + 1;. Commented Feb 2, 2015 at 12:18
  • Why use the referencing clause if you're just going to use the same names? Delete that clause entirely. Commented Feb 2, 2015 at 12:18
  • What do you mean by "dedupe trigger"? It looks like you mean you want to update an existing row in the situation where the user tries to insert a new row with the same key. This isn't possible with a trigger on a table in Oracle because the insert will still happen. Commented Feb 2, 2015 at 12:22

1 Answer 1

-2

you should remove fragment: "USER.". Properly is:

CREATE OR REPLACE TRIGGER TBL_ACTION_DEDUP
BEFORE INSERT
ON ERRORTABLE 
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN  
IF (:OLD.Identifier = :NEW.Identifier) THEN  
  SELECT :OLD.tally + 1 INTO :NEW.tally FROM DUAL;
END IF;  
END TBL_ACTION_DEDUP;
/
3
  • Thanks Franek, it worked. But I have a question. The intention of doing this trigger is to implement dedup, but when I tried to input two similar rows, it returns the error "ORA-00001: unique constraint (UPM_USER.PKEY_ERRORID) violated". I am expecting this will not happen because the trigger will automatically dedup my entry.
    – user60216
    Commented Mar 7, 2014 at 9:11
  • I suppose primary key PKEY_ERRORID doesn't contain column TALLY. You should extend this primary key.
    – rtbf
    Commented Mar 7, 2014 at 10:12
  • Hmm, after rethink you should query also for old tally because in this trigger shape old nothing return. SELECT tally + 1 INTO :NEW.tally FROM ERRORTABLE where identifier = : NEW.identifier
    – rtbf
    Commented Mar 7, 2014 at 13:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.