在MySQL Trigger中计算后，变量变为NULL [英] Variable is getting NULL after calculations in MySQL Trigger

问题描述

我有一个名为 Initial_Fees 的表。此表中有一列名为 Initial_Consult_Fee ，每年不得超过30,000。下面是我的表格。

I have a table called Initial_Fees. There is a column in this table called Initial_Consult_Fee where it cannot be more than 30,000 per year. Below is my table.

    CREATE TABLE `initial_fees` (
 `idInitial_Fees` int(11) NOT NULL AUTO_INCREMENT,
 `idPortfolio` int(11) NOT NULL,
 `Current_Time_Stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `Initial_Gross_Fee` double NOT NULL,
 `Initial_Consult_Fee` double NOT NULL,
 `Updated_Date` date NOT NULL,
 `idTransactions` int(11) DEFAULT NULL,
 PRIMARY KEY (`idInitial_Fees`)
)

所以，我创建了一个触发器，它将进行检查，无论 Initial_Consult_Fee 是否超过30,000。除此之外，它将舍入所有值。

So, I created a trigger, that will do the check, whether the Initial_Consult_Fee is above 30,000 or not. Apart from that, it will round all the values.

DELIMITER $$
CREATE TRIGGER `Initial_Fees_BINS` BEFORE INSERT ON `Initial_Fees` FOR EACH ROW
begin
    DECLARE `initial` DOUBLE;
    DECLARE `ongoing` DOUBLE;
    DECLARE `total` DOUBLE;
    DECLARE `valueToBeEntered` DOUBLE;

    SET `initial`:=(SELECT SUM(`Initial_Consult_Fee` ) FROM `Initial_Fees` WHERE `Updated_Date` BETWEEN MAKEDATE(YEAR(New.Updated_Date),1) AND MAKEDATE(YEAR(New.Updated_Date),365));
    SET `ongoing`:=(SELECT SUM(`Ongoing_Consult_Fee` ) FROM `Ongoing_Fees` WHERE `Updated_Date` BETWEEN MAKEDATE(YEAR(New.Updated_Date),1) AND MAKEDATE(YEAR(New.Updated_Date),365));
    SET `total` := `initial`+`ongoing`;

    IF((New.Initial_Consult_Fee+`total`) > 30000) THEN
        SET `valueToBeEntered`:= 30000 - `total`;
    END IF;

    IF((New.Initial_Consult_Fee+`total`) < 30000) THEN
        SET `valueToBeEntered`:= New.Initial_Consult_Fee;
    END IF;


SET New.Initial_Gross_Fee = ROUND(New.Initial_Gross_Fee,2),
New.Initial_Consult_Fee = ROUND(`valueToBeEntered`,2);
END;

然而，当我输入数据时，以及 New.Initial_Vision_Fee + total 我收到以下错误

However, when I enter data, and if the New.Initial_Vision_Fee+total I get the below error

Column 'Initial_Consult_Fee' cannot be null

我的代码出了什么问题？

What is wrong with my code?

推荐答案

一些评论。

首先，您要查看所有投资组合的总咨询费用。这似乎不寻常。它确实符合您的描述，但我希望总数基于某个因素，例如投资组合。

First, you are looking at the total consulting fees across all portfolios. That seems unusual. It does conform to your description, but I would expect the total to be based on some factor, such as the portfolio.

其次，当没有时，您的问题就出现了匹配 ingoing 或传出表。然后你的变量的值是 NULL 。

Second, your problem is occurring when there are no matches in the ingoing or outgoing tables. Then the values of your variables are NULL.

你可以通过做类似的事情来解决这个问题：

You can readily fix this by doing something like:

 SET `total` := coalesce(`initial`, 0) + coalesce(`ongoing`, 0);

这篇关于在MySQL Trigger中计算后，变量变为NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！