title: How to assign a foreign key value using a a before insert trigger
body: <p>I have a scenario like this:</p>
  
  <p>There are two tables <code>table1</code> and <code>table2</code>. The <code>table1</code> has a primary key <code>pkey</code> and <code>table2</code> has a foreign key <code>fkey</code> now during an insert if the foreign key is provided the value should be inserted as it is. Otherwise, it has to get a primary key from <code>table1</code> using some computation and determine the foreign key to be inserted. How do i do this??</p>
  
  <p>I am using MySql 5.0</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>In my scenario, <code>table1</code> holds the billing details, that is, the <code>table1</code> has the bills and the total amount to be paid. The customer pays some amount of the total outstanding balance or will pay for a particular bill. What i want to do here is. When i am not provided with a <code>bill_id</code> (which is primary key in <code>table1</code> and foreign key in <code>table2</code>) i would like to search for the oldest bill that is due in <code>table1</code> and deduct the amount due and further deduct the remaining amount if any from the next bill in the billed order. I want to do this in the database layer rather than the upper layer. So when an insert is being done without a value for the foreign key the value should be retrieved and placed by the trigger or otherwise directly inserted. How do i achieve this?</p>
  
  <p>Using the answers provided here, i tried this:</p>
  
  <pre><code>CREATE DEFINER=`root`@`localhost` TRIGGER `inflow_pay_done_insert` BEFORE INSERT ON `inflow_pay_done` FOR EACH ROW BEGIN
      DECLARE pkey INT;
      SET pkey = (SELECT bill_id from inflow_bills where payment_stat = 0 and rs_id = NEW.rs_id order by time_stamp limit 1);
      SET NEW.bill_id = IF(NEW.bill_id , NEW.bill_id , pkey);
      UPDATE raw_mat_sup rms SET rms.outstanding_bal_payable = rms.outstanding_bal_payable - NEW.amount where rms.rs_id = NEW.rs_id;
  END|
  </code></pre>
  
  <p>and i am getting the following error when i am trying to insert in inflow_pay_done:</p>
  
  <p>/* SQL Error (1048): Column 'bill_id' cannot be null */</p>