PLS-00049 bad bind variable when compiling a trigger

oracleplsqltrigger

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'

Best Answer

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

Related Solutions

MySQL using trigger instead of check

You do not need a RETURN 1 because Trigger is a Stored Procedure, not a Stored Function.

If you want to break it on purpose, that's acceptable.

I wrote two posts about how to break a trigger midstream

Trigger in MySQL to prevent insertion (Apr 25, 2011)
check constraint does not work? (Dec 23, 2011)

Try this:

CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER `test`
BEFORE UPDATE ON `apply` 
FOR EACH ROW
BEGIN
    DECLARE dummy INT;

    IF NEW.cname = "hi" THEN          
        SET NEW.cname = "hello";
    ELSE
        SELECT no_such_column INTO dummy
        FROM information_schema.no_such_table;
    END IF;

END; $$

Bad bind variable in trigger

The code should read:

create or replace trigger complaint_advertise
BEFORE INSERT ON COMPLAINT_INFO FOR EACH ROW
DECLARE
BEGIN
    UPDATE ADVERTISEMENT SET AD_APPEAR='INVALID' WHERE AD_ID=:NEW.AD_ID;
END;
/

Best Answer

Related Solutions

MySQL using trigger instead of check

Bad bind variable in trigger

Related Question