PLS-00049 bad bind variable when compiling a trigger

Question

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'

• miracle173: What is the old value of IDENTIFIER and TALLY if you insert a new row? Old values only exist for update and delete triggers.
• Colin 't Hart: Why are you using a select ... from dual to assign the new value? Much better just to do :new.tally = :old.tally + 1;.
• Colin 't Hart: Why use the referencing clause if you're just going to use the same names? Delete that clause entirely.
• Colin 't Hart: 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.

Answer 1

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;
/

• user60216: 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.
• rtbf: I suppose primary key PKEY_ERRORID doesn't contain column TALLY. You should extend this primary key.
• rtbf: 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