如何检查插入的值是否不超过100

Question

下面的代码检查并计算数据库中特定成员的百分比。总百分比是100。如果

成员的记录值等于100，我希望代码停止插入

$stmto = $sql->Execute($sql->Prepare("SELECT SUM(B_PERCENTAGE) AS PERCENT FROM daa_beneficiaries WHERE B_M_ID  = " . $sql->Param('a') . ""), [$memberid]);
$obj = $stmto->FetchNextObject();
$mypercentage = $obj->PERCENT;
$totalpercentage = 100;

if ($mypercentage <= $totalpercentage) {
$sql->Execute($sql->Prepare("INSERT INTO daa_beneficiaries (B_SURNAME,B_MAIDENN,B_OTHERN,B_CITY,B_SUBURB,B_LANDMARK,B_MOBILE,B_EMAIL,B_GPS,B_DOB,B_RELATIONSHIP,B_PERCENTAGE,B_M_ID) VALUE(" . $sql->Param('a') . "," . $sql->Param('b') . "," . $sql->Param('c') . "," . $sql->Param('d') . "," . $sql->Param('d') . "," . $sql->Param('e') . "," . $sql->Param('f') . "," . $sql->Param('g') . "," . $sql->Param('h') . "," . $sql->Param('i') . "," . $sql->Param('j') . "," . $sql->Param('k') . "," . $sql->Param('i') . ") "), array($sur_name, $maiden_name, $other_name, $city_name, $location_name, $landmark, $mobile_no, $email_address, $gps_address, $benef_dob, $b_relationship, $percentage, $memberid));
print $sql->ErrorMsg();
$msg = "Beneficiary has been saved successfully";
$status = "success";
} else {
$msg = "Beneficiary must not exceed 100%";
$status = "error";
}

Answer 1

您可以创建BEFORE INSERT触发器，当总数超过100时引发错误。

的例子:

CREATE TRIGGER check_total_percentage
BEFORE INSERT
ON test
FOR EACH ROW
BEGIN
IF 100 < COALESCE((SELECT SUM(test.percentage) + NEW.percentage
FROM test
WHERE test.user_id = NEW.user_id), NEW.percentage) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Total percentage exceeds 100.';
END IF;
END

演示