MySQL Data Validation on Insertion

mysql

Solution

Say you have a couple tables:

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

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

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

Problem

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. 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?

Original source