Bad bind variable in trigger

oracletrigger

i'm trying to update the table whenever complaint has been registered

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;

But, getting error ERROR at line 2: PLS-00049: bad bind variable 'AD_ID'

Best Answer

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

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 audit trail trigger in Oracle 11g R2

If you write :new.colName than you are referencing the new value of a column named "COLNAME" of table tbl_mst_books. But table tbl_mst_books has no column named "COLNAME". Actually there is no possibility to do this this way because dynamic sql does not work with :new and :old bind values. You have to program it explicitly for every column in your table and have to change your code if your table structure changes.

see AskTom

Best Answer

Related Solutions

MySQL using trigger instead of check

Bad bind variable in audit trail trigger in Oracle 11g R2

Related Question