mySQL触发器在控制台插入后起作用，但在脚本插入后不起作用 [英] mySQL Trigger works after console insert, but not after script insert

问题描述

我有一个触发器问题.

我设置了一个触发器，用于在插入表之后更新其他表.

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

如果我从MySQL控制台进行插入，则一切正常，但是，即使我从外部python脚本进行插入(即使具有相同的数据)，触发器也不会执行任何操作，如您所见.

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.

我尝试将Definer更改为'user'@'％'和'root'@'％'，但是它仍然无济于事.

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.

在您的python脚本中，打印出python发送给MySQL以便执行的SQL语句，以确保它按您期望的方式构造-例如，如果NEW.type不等于'PRE'，则触发器将具有执行，但不会导致任何更新.

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.

还请确保您正在检查插入内容中的错误.我不是python程序员，所以我无法告诉您该怎么做，但是这似乎是您想要的.

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 (NEW.type = 'PRE') THEN块并进行简单的修改，例如将NEW.type设置为"debug".确保触发器确实执行后，请依次进行测试，并重新添加更多的真实代码，直到找出问题所在为止.

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.

此外，Marcos评论，如果脚本在成功完成后没有自动提交，我会感到惊讶.确实，我会就任何脚本/语言发表这一声明.

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.

这篇关于mySQL触发器在控制台插入后起作用，但在脚本插入后不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！