title: MySql conditional Insert with dynamic values
body: <p>So before I explain my question, Here are a couple stripped down table definitions to help illustrate my issue:</p>
  
  <pre><code>-- Holds data about different memberships
  CREATE TABLE IF NOT EXISTS `Member_Types` ( 
  `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT, 
  `Name` VARCHAR(20) NOT NULL,  
  `Description` VARCHAR(255) NOT NULL, 
  `Member_Limit` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0', 
  PRIMARY KEY( `ID` )
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  
  INSERT INTO `Member_Types` ( `ID`, `Name`, `Description`, `Member_Limit` ) VALUES 
  ( 1, 'General Member', 'Description of this membership.', 00 ), 
  ( 2, 'Extended Member', 'Description of this membership.', 00 ), 
  ( 3, 'Premium Member', 'Description of this membership.', 00), 
  ( 4, 'General Administrator', 'Description of this membership.', 05 ), 
  ( 5, 'Master Administrator', 'Description of this membership.', 01 );
  
  -- Stores the basic data about our site members
  CREATE TABLE IF NOT EXISTS `Member_Infos` ( 
  `ID` BIGINT(8) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT, 
  `Username` VARCHAR(30) NOT NULL UNIQUE, 
  `Password` CHAR(41) NOT NULL, 
  `EmailAddr` VARCHAR(100) NOT NULL, 
  `Type_ID` INT UNSIGNED NOT NULL, 
  `Salt_ID` BIGINT(8) UNSIGNED ZEROFILL NOT NULL, 
  PRIMARY KEY( `ID` ), 
  FOREIGN KEY( `Type_ID` ) REFERENCES `Member_Types` ( `ID` )
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  </code></pre>
  
  <p>The Member_Infos table contains a foreign key that binds Member_Infos.Type_ID = Member_Types.ID</p>
  
  <p>The Member_Types table has a column Member_Limit which contains an integer that represents the maximum number of records that the Member_Infos table
  can contain where its Type_ID is equal to Member_Types.ID</p>
  
  <p>I was able to write a check constraint but apparently mysql doesn't enfore check constraints.
  I would like to write a trigger that checks that the count of records in the member_infos table is &lt;= the Member_Types.Member_Limit before inserting.
  for example: using the data above, record 4 has a Member_Limit = 5. If a new record is attempted to be inserted into the Member_Infos table that
  has a Type_ID = 4 and a count of records in the Member_Infos table with Type_ID &lt;= 5 than the data is inserted, otherwise it is rejected.
  Any advice would be greatly appreciated.</p>