插入时的MySQL数据验证

问题描述：

我不知道MySQL(或任何相关的数据库)是否可以做到这一点，但我认为它可以做到.

I don't know if MySQL (or any DB for that matter) can do this, but I'm assuming it can be done.

我有一张桌子，有多个字段.这些字段中的一个跟踪可用的项目"总数，另一个保持当前正在使用的数量.

I have a table, with multiple fields. One of these fields tracks the total number of available 'items', and another holds how many are currently in use.

是否有可能在UPDATE语句中验证传入的数据，以便如果正在使用的项目数变得大于可用的总数，则UPDATE将失败? IE可以根据其他字段的内容向字段添加数值限制吗?

Is it possible to validate incoming data in an UPDATE statement, such that the UPDATE will fail if the number of items in use would become greater than the total available? IE can I add numerical limits to a field based on the contents of another field?

答

假设您有几个表格:

Items
------------
ItemID
NumAvailable
-------------

Checkout
-----------
UserID
ItemID
-----------

您可以创建一个触发器 ItemID并与该特定项目的NumAvailable比较.它看起来像这样(可能有错误，仅提出一般性想法:).错误方法来自

You could create a trigger that sums the ItemID and compares to the NumAvailable for that particular item. It would look something like this (may have errors, general idea presented only :) . The method for error gleaned from here, there may be a better way available):

CREATE TRIGGER check_available 
BEFORE INSERT ON Checkout 
FOR EACH ROW 
BEGIN
  SELECT IF (COUNT(new.ItemID) > Items.NumAvailable) THEN
    DECLARE dummy INT;
        SELECT 'No more items to check out!' INTO dummy 
  FROM new NATURAL JOIN Items WHERE NEW.ItemID = Items.ItemID
  END IF;
END

插入时的MySQL数据验证

相关推荐