Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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'

share|improve this question
    
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 Apr 6 at 6:44

1 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;
/
share|improve this answer
    
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 Mar 7 at 9:11
    
I suppose primary key PKEY_ERRORID doesn't contain column TALLY. You should extend this primary key. –  Franek Mar 7 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 –  Franek Mar 7 at 13:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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