MySQL Trigger Update with select from another table
Just learning about triggers and I'm created the following trigger;
CREATE TRIGGER `incremental_before_ins_tr` BEFORE INSERT ON incremental`
FOR EACH ROW
BEGIN
SET NEW.source = (Select source from crm_record
where msisdn = new.msisdn order by dat DESC limit 1);
END;
However the value does not appear to be getting updated. Any ideas?
I've actually managed to solve this myself. Here is the updated code
CREATE TRIGGER `incremental_before_ins_tr` BEFORE INSERT ON `incremental`
FOR EACH ROW
BEGIN
SET NEW.source = (Select source from crm_record
where crm_record.msisdn = new.msisdn order by dat DESC limit 1);
END;
I needed to specify the table name prior to the column value on line 5.
Looks like you have a typo.
You have entered incremental with a trailing backtick instead of enclosing it in backticks.
It is likely that your trigger is now bound to a table called incremental`
which I am assuming does not exist.
Since you have ruled out the above. I see that the UPDATE keyword is missing. Add UPDATE table
before your SET line. Replace table with the name of your table.
精彩评论