MySQL Trigger - INSERT on condition of UPDATE

Question

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.

Michael · Accepted Answer · 2011-05-19 16:05:32Z

1

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;

answered May 19, 2011 at 16:05

Michael

1831 gold badge2 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ira Rainey · Accepted Answer · 2011-05-19 08:08:54Z

0

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.

answered May 19, 2011 at 8:08

Ira Rainey

5,2152 gold badges36 silver badges42 bronze badges

1 Comment

Michael Over a year ago

Thanks Ira, I will write up the code and post back here with my results

Collectives™ on Stack Overflow

MySQL Trigger - INSERT on condition of UPDATE

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related