MYSQL 触发器有关问题-MySQL教程-爱易网页

MYSQL 触发器有关问题

日期：2014-05-16　浏览次数：20892 次

MYSQL 触发器问题

SQL code

create trigger amountlog_update_recalculated before update on dm_amountlog for each row
BEGIN
    SET @cur=curdate();
    set NEW.actualyear=year(@cur);
    set NEW.actualmonth=month(@cur);
    set NEW.actualday=day(@cur);
    SET @suma=dm_amount.paid+NEW.actualpayment;
    update dm_amount set dm_amount.paid=@suma where dm_amount.docid=NEW.docid;
END;

这个触发器更新的时候为什么提示找不到表“dm_amount”，但这张表确实是存在的，而且也没有错误

还有下面一个触发器，为什么总提示出错，上面的触发器是是因为下面这个触发器问题解决不了所以拆出来的

SQL code

create trigger amountlog_update_recalculated before update on dm_amountlog for each row
BEGIN
    SET @cur=curdate();
    set NEW.actualyear=year(@cur);
    set NEW.actualmonth=month(@cur);
    set NEW.actualday=day(@cur);
    SET @suma=select sum(dm_amountlog.actualpayment) from dm_amountlog where dm_amountlog.docid=NEW.docid;
    update dm_amount set dm_amount.paid=@suma where dm_amount.docid=NEW.docid;
    IF dm_amount.paid=dm_amount.payment where dm_amount.docid=NEW.docid THEN
        update dm_amount SET dm_amount.isdone=1 where dm_amount.docid=NEW.docid;
    END IF;
END;
$$

望大侠指教

------解决方案--------------------

引用还有下面一个触发器，为什么总提示出错，上面的触发器是是因为下面这个触发器问题解决不了所以拆出来的

------解决方案--------------------
DELIMITER $$
CREATE TRIGGER amountlog_update_recalculated BEFORE UPDATE ON dm_amountlog
FOR EACH ROW
BEGIN
SET @cur=CURDATE();
SET NEW.actualyear=YEAR(@cur);
SET NEW.actualmonth=MONTH(@cur);
SET NEW.actualday=DAY(@cur);
SET @suma=(SELECT SUM(dm_amountlog.actualpayment) FROM dm_amountlog WHERE dm_amountlog.docid=NEW.docid);
UPDATE dm_amount SET dm_amount.paid=@suma WHERE dm_amount.docid=NEW.docid;
IF dm_amount.paid=dm_amount.payment AND dm_amount.docid=NEW.docid THEN
UPDATE dm_amount SET dm_amount.isdone=1 WHERE dm_amount.docid=NEW.docid;
END IF;
END;$$
DELIMITER ;

免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。

MYSQL 触发器有关问题

相关资料更多>

推荐阅读更多>