Mysql is causing update problems
I am considering using triggers on my database to track order changes. Every time you add an order, I add a point to the table that stores the estimate in "possession".
When I run this command, I can change the ID before adding the trigger.
UPDATE bookit SET prov_id = 32 where book_id=2;
after adding the following trigger i get can't update prov_id and get this error
1054 - Unknown column "bookit.prov_id" in clause "where"
Trigger:
delimiter //
CREATE TRIGGER update_tenure_it BEFORE
UPDATE ON bookit FOR EACH ROW
BEGIN
if(new.prov_id<>old.prov_id) THEN
UPDATE provider_score set tenure=(tenure+1) where bookit.prov_id=provider_id;
END IF;
END;
//
Do you know what I am doing wrong and where is the problem? Should I even consider using triggers for such an operation, or just code in PHP? Thank!
source to share
In triggers, you do not have direct access to the table via TABLENAME.FIELD
; instead, refresh triggers give you old
. * and new
. * as line aliases before and after update, respectively. In this case, the following should work:
delimiter //
CREATE TRIGGER update_tenure_it BEFORE
UPDATE ON bookit FOR EACH ROW
BEGIN
if(new.prov_id<>old.prov_id) THEN
UPDATE provider_score set tenure=(tenure+1) where new.prov_id=provider_id;
END IF;
END;
//
source to share