MySQL Trigger - INSERT on condition of UPDATE

I'm trying to find the most effecient way of inserting data into another table when a particular field is updated on trigger table. The INSERT should only occur on a specific type of update.

The table on which I want to create the trigger is named incremental. The table I'm inserting into is named crm_record

On incremental there is a field called status. By default when a record is initially added to the table the status field is set to new. After billing has processed that value changes to processed. So once this occurs I want to INSERT into crm_record, only if the value of another field (success) is set to 1.

I have considered using both开发者_开发百科 CASE and IF but would like an expert's opinion on the best way to do this.

Ok, I eventually went with this that seemed to work. Thanks for pointing me in the right direction

CREATE TRIGGER `incremental5_after_ins_tr_crmm` AFTER UPDATE ON `incremental5`
FOR EACH ROW
BEGIN
IF Status = 'processed' AND Success = 1 THEN
INSERT INTO crm_master (msisdn,source,contract_type,revenue) VALUE    (new.msisdn,'INC5',new.contract_type,revenue=revenue+2.5)
ON DUPLICATE KEY UPDATE contract_type=new.contract_type,revenue=revenue+2.5;
END IF;
END;

All you need to do is to create an AFTER UPDATE trigger and test the value of status and success together. If it's going only going to be one state you're testing for then an IF statement would be the simplest way to go about it.

However before implementing a trigger it's always worth going back a step and checking to see if the row in crm_record shouldn't actually be inserted via the code logic when the status and success columns are updated.