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'
Colin 't Hart
9,51015 gold badges37 silver badges44 bronze badges
asked Mar 6, 2014 at 8: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;
/
-
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– user602162014年03月07日 09:11:44 +00:00Commented Mar 7, 2014 at 9:11
-
I suppose primary key PKEY_ERRORID doesn't contain column TALLY. You should extend this primary key.rtbf– rtbf2014年03月07日 10:12:26 +00:00Commented 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.identifierrtbf– rtbf2014年03月07日 13:32:13 +00:00Commented Mar 7, 2014 at 13:32
lang-sql
select ... from dual
to assign the new value? Much better just to do:new.tally = :old.tally + 1;
.referencing
clause if you're just going to use the same names? Delete that clause entirely.