How to limit the input value of another column according to the value entered in the column-CodePudding

I have a data_type table and a data table. The column structure is as follows.

data_type    data
=========    ====================
id | Type    Type_ID | data_count
---------    --------------------
 1 | a             1 | 50
 2 | b             2 | 100
 3 | c             3 | 30

When I add a new row to a data table, I want to set a different limit on the number of data_ that can be input according to the type_id.

for example In type a, only integers between 50 and 100 can be entered. I want to be able to input 0 to 100 for type b and 10 to 30 for type c.

Is it possible to put such a limit when doing an INSERT INTO?

For reference, below is my current code. DAO.js

const createRecordData = async (userId, a1, a2, typeId, count) => {
  await myDataSource.query(
    `INSERT INTO a (user_id,a1,a2)
  VALUES (?,?,?)`,
    [userId, a1, a2]
  );

  await myDataSource.query(`
  DELIMITER $$
  CREATE TRIGGER checkValueTBL 
  BEFORE INSERT ON TBL FOR EACH ROW
  BEGIN
    IF NEW.data_type = 1 THEN
      IF NEW.datas < 10 OR NEW.datas > 90 THEN
        SIGNAL SQLSTATE '45015' 
        SET message_text = 'Type a: value not between 10 and 90';
    END IF;
    ELSEIF NEW.data_type = 2 THEN
      IF NEW.datas < 30 OR NEW.datas > 50 THEN
      SIGNAL SQLSTATE '45015' 
      SET message_text = 'Type a: value not between 30 and 50';
    END IF;
    ELSEIF NEW.data_type = 3 THEN
      IF NEW.datas < 60 OR NEW.datas > 200 THEN
      SIGNAL SQLSTATE '45015' 
      SET message_text = 'Type a: value not between 60 and 200';
    END IF;
  END $$
  DELIMITER ;`);

  const datas = await myDataSource.query(
    `INSERT INTO data (a_id,typeId,count)
  VALUES ((SELECT LAST_INSERT_ID()),?,?)`,
    [typeId, count]
  );
  return datas;
};

If you write the above, you will get the following error.

     "err": "ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER $$\n CREATE TRIGGER checkValueTBL \n BEFORE INSERT ON TBL FOR EACH ROW' at line 1"

I need help.

CodePudding user response：

You could do that with a BEFORE INSERT trigger (see here for a short tutorial)

DELIMITER $$

CREATE TRIGGER `checkValueTBL` BEFORE INSERT ON `TBL` FOR EACH ROW
BEGIN
IF NEW.data_type = 1 THEN
     IF NEW.data_value < 10 OR NEW.data_value > 25 THEN
         SIGNAL SQLSTATE '45015' 
             SET message_text = 'Type a: value not between 10 and 25';
     END IF;
ELSEIF NEW.data_type = 2 THEN
     ...
ELSEIF NEW.data_type = 3 THEN
     ...
END IF;

END $$

DELIMITER ;

You can also retrieve the numeric value from a different table.

Inserting an out of bounds value will trigger an error (if you do that with INSERT IGNORE, the insert will silently fail, and the data will not be input.

You can also correct any value(s) if you prefer (i.e., if it is less than 10, set it to 10, if it is above 123, set it to 123, and so on): omit the SIGNAL statement and the insert will proceed with the altered values.