mysql触发器调用python,mySQL触发器在控制台插入后有效，但在脚本插入后无效

I have a problem with a trigger.

I set up a trigger for update other tables after an insert in a table.

If I make an insert from MySQL console, all works fine, but if I do inserts, even with the same data, from an external python script, the trigger does nothing, as you can see bellow.

I tried changing the Definer to 'user'@'%' and 'root'@'%', but it's still doing nothing.

mysql> select vid_visit,vid_money from videos where video_id=487;

+-----------+-----------+

| vid_visit | vid_money |

+-----------+-----------+

| 21 | 0.297 |

+-----------+-----------+

1 row in set (0,01 sec)

mysql> INSERT INTO `table`.`validEvents` ( `id` , `campaigns_id` , `video_id` , `date` , `producer_id` , `distributor_id` , `money_producer` , `money_distributor` , `type` ) VALUES ( NULL , '30', '487', '2010-05-20 01:20:00', '1', '0', '0.009', '0.000', 'PRE' );

Query OK, 1 row affected (0,00 sec)

mysql> select vid_visit,vid_money from videos where video_id=487;

+-----------+-----------+

| vid_visit | vid_money |

+-----------+-----------+

| 22 | 0.306 |

+-----------+-----------+

DROP TRIGGER IF EXISTS `updateVisitAndMoney`//

CREATE TRIGGER `updateVisitAndMoney` BEFORE INSERT ON `validEvents`

FOR EACH ROW BEGIN

if (NEW.type = 'PRE') THEN

SET @eventcash=NEW.money_producer + NEW.money_distributor;

UPDATE campaigns SET cmp_visit_distributed = cmp_visit_distributed + 1 , cmp_money_distributed = cmp_money_distributed + NEW.money_producer + NEW.money_distributor WHERE idcampaigns = NEW.campaigns_id;

UPDATE offer_producer SET ofp_visit_procesed = ofp_visit_procesed + 1 , ofp_money_procesed = ofp_money_procesed + NEW. money_producer WHERE ofp_video_id = NEW.video_id AND ofp_money_procesed = NEW. campaigns_id;

UPDATE videos SET vid_visit = vid_visit + 1 , vid_money = vid_money + @eventcash WHERE video_id = NEW.video_id;

if (NEW.distributor_id != '') then

UPDATE agreements SET visit_procesed = visit_procesed + 1, money_producer = money_producer + NEW.money_producer, money_distributor = money_distributor + NEW.money_distributor WHERE id_campaigns = NEW. campaigns_id AND id_video = NEW.video_id AND ag_distributor_id = NEW.distributor_id;

UPDATE eventForDay SET visit = visit + 1, money = money + NEW. money_distributor WHERE date = SYSDATE() AND campaign_id = NEW. campaigns_id AND user_id = NEW.distributor_id;

UPDATE eventForDay SET visit = visit + 1, money = money + NEW.money_producer WHERE date = SYSDATE() AND campaign_id = NEW. campaigns_id AND user_id= NEW.producer_id;

ELSE

UPDATE eventForDay SET visit = visit + 1, money = money + NEW. money_producer WHERE date = SYSDATE() AND campaign_id = NEW. campaigns_id AND user_id = NEW.producer_id;

END IF;

END IF;

END

//

解决方案

I think that it's far more likely that you are encountering an uncaught error, rather than that the trigger is not executing, particularly since it executes successfully from the console.

You need to isolate where the error occurs - in the trigger itself, or in the calling script.

In your python script, print out the SQL statement that python sends to MySQL for execution in order to ensure that it is constructed as you expect - for example, if NEW.type does not equal 'PRE', the trigger will have executed, but will not result in any updates.

Also ensure that you are checking for errors on the insert. I'm not a python programmer, so I can't tell you how that is done, but this seems to be what you're looking for.

If neither of these leads you to the problem, comment out the whole if (NEW.type = 'PRE') THEN block and do a simple modification, such as setting NEW.type to 'debug'. After ensuring that the trigger does in fact execute, retest successively with more of the real code added back in until you isolate the problem.

Also, wrt Marcos comment, I would be surprised if the script didn't auto-commit upon successful completion. Indeed, I would make this statement about any script/language.