How to check insert conditions other than before insert triggers-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 adding a new row to a datatable, I want to set a different limit on the number of data_ that can be entered based on the type_id.

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

If it is out of this range, the following message is returned with an error response (400).

'Type a: value not between 10 and 30'

For reference, below is my current code. Is there a way to put a limit on the code right away, without using a before insert trigger?

If there is a way, what would be better performance than using a before insert trigger?

const createData = async(typeId, count) => {
   const data = await myDataSource.query(
     `INSERT INTO data (type_id, data_count)
     VALUES(?, ?)`,
     [typeId, count],
   );
   return data;
};

I need help

CodePudding user response：

You could handle the logic in your app code:

const createData = async (typeId, count) => {
  switch (typeId) {
    case 1:
      if (count < 50 || count > 100) {
        return res.status(400).send('Type a: value not between 50 and 100');
      }
      break;
    case 2:
      if (count < 0 || count > 100) {
        return res.status(400).send('Type b: value not between 0 and 100');
      }
      break;
    case 3:
      if (count < 10 || count > 30) {
        return res.status(400).send('Type c: value not between 10 and 30');
      }
      break;
    default:
      return res.status(400).send('Unrecognized type: '   typeId);
  }
  const data = await myDataSource.query(
    `INSERT INTO data (type_id, data_count)
      VALUES(?, ?)`,
    [typeId, count]
  );
  return data;
};