5

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?

Michael
  • 183
  • 1
  • 2
  • 7

2 Answers2

10

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.

Michael
  • 183
  • 1
  • 2
  • 7
0

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.

Jared
  • 12,406
  • 1
  • 35
  • 39
  • That's actually a typo in this entry, it was bound to the correct table – Michael May 18 '11 at 20:13
  • Tried that and got the following error **Can't update table incremental in stored function /trigger because it is already used by statement which invoked this function/trigger** – Michael May 19 '11 at 07:04