我有一个问题。
我在表格中插入一个字后建立了更新其他表格的触发点。
如果我从MySQL console上插入一个字句,那么所有工作都会受到罚款,但如果我确实用同一数据从外面的字典上插入,那么触发器就没有什么作用,因为你可以看到灯塔。
我试图将定义者改变为用户......%和根基...... %,但至今没有做任何事。
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
//