title: Cannot fetch a value from a SELECT query inside a trigger
body: <p>I am writing a trigger to UPDATE a row in a table AFTER INSERT on another table. Here are the scripts of the tables:</p>
  
  <p>Table <strong>inv_cost</strong></p>
  
  <pre><code>CREATE TABLE inv_cost (
    Username varchar(20) NOT NULL DEFAULT '',
    MachineType varchar(2) NOT NULL,
    Cost smallint(4) NOT NULL DEFAULT '0',
    PRIMARY KEY (Username,MachineType),
  )
  </code></pre>
  
  <p>Table <strong>investigation</strong></p>
  
  <pre><code>CREATE TABLE investigation (
    Username varchar(20) NOT NULL,
    MachineType varchar(2) NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  DELIMITER ;;
  CREATE TRIGGER TRG1 AFTER INSERT ON investigation FOR EACH ROW BEGIN
      DECLARE cost INT DEFAULT 0;
      SET cost = (SELECT Cost FROM inv_cost WHERE Username = NEW.Username AND MachineType = NEW.MachineType);
      UPDATE test SET Balance = Balance - cost WHERE Username = New.Username;
  END;;
  DELIMITER ;
  </code></pre>
  
  <p>Table <strong>investigator</strong></p>
  
  <pre><code>CREATE TABLE test (
    ID int(10) unsigned NOT NULL AUTO_INCREMENT,
    Username varchar(20) NOT NULL DEFAULT '',
    Balance smallint(6) DEFAULT NULL,
    PRIMARY KEY (ID)
  ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
  DELIMITER ;;
  CREATE TRIGGER TRG2 AFTER UPDATE ON test FOR EACH ROW BEGIN
      IF NEW.Balance &gt; OLD.Balance THEN
          INSERT INTO payments SET Username = NEW.Username, PaymentOn = NOW(), Amount = NEW.Balance - OLD.Balance;
      END IF;
  END;;
  DELIMITER ;
  </code></pre>
  
  <p>The problem with trigger <strong>TRG1</strong> is that it does not compute the value of the variable <strong>cost</strong> from the SELECT statement, and always takes the value 0 or whatever is set to DEFAULT in its declaration. The UPDATE query on the next line works good (with whatever value <strong>cost</strong> takes from its declaration OR if it is assigned a const value like <code>SET cost = 100</code>;). The SELECT run separately gives the required value of <strong>Cost</strong>.</p>
  
  <p>What is wrong with this code?</p>
  
  <p>Thanks in advance.</p>