title: mysql TRIGGER update field after update
body: <p>i have 2 table today_plan and kiln_master. </p>
  
  <p>after  pattern  and itemno inserted  to today_plan then need to select matching <strong>yeild from kiln_master</strong> table and update this value  to  <strong>yeild field in today_plan</strong>. </p>
  
  <p>i have created a trigger </p>
  
  <pre><code>CREATE TRIGGER update_yeild AFTER INSERT ON today_plan
  FOR EACH ROW UPDATE today_plan
    SET yeild= (SELECT kiln_master.yeild from kiln_master,today_plan WHERE today_plan.itemno = kiln_master.item  AND  today_plan.pattern = kiln_master.pattern ) WHERE  itemno=new.itemno AND pattern=new.pattern
  </code></pre>
  
  <p>what part is wrong with my code   </p>
  
  <p><strong>today_plan</strong></p>
  
  <pre><code>DROP TABLE IF EXISTS decsys.today_plan;
  CREATE TABLE `today_plan` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `belt` varchar(25) NOT NULL,
    `distant` varchar(25) NOT NULL,
    `pjtno` varchar(15) DEFAULT NULL,
    `pattern` varchar(25) NOT NULL,
    `itemno` varchar(25) NOT NULL,
    `pro_qty` varchar(25) NOT NULL,
    `act_qty` varchar(25) NOT NULL,
    `yeild` varchar(25) NOT NULL,
    `remark` varchar(100) NOT NULL,
    `shipment` varchar(15) NOT NULL,
    `temp` varchar(15) NOT NULL,
    `fire_date` date NOT NULL,
    `kiln` varchar(10) DEFAULT NULL,
    `kiln_plan` varchar(15) NOT NULL,
    `kiln_act` varchar(15) NOT NULL,
    `ins_date` date NOT NULL,
    `ins_act` varchar(15) NOT NULL,
    `plandate` date NOT NULL,
    `ship_date` date NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=MyISAM AUTO_INCREMENT=31 DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p><strong>kiln_master</strong></p>
  
  <pre><code>DROP TABLE IF EXISTS decsys.kiln_master;
  CREATE TABLE `kiln_master` (
    `kid` int(7) NOT NULL,
    `pattern` varchar(30) NOT NULL,
    `item` varchar(30) NOT NULL,
    `yeild` double NOT NULL,
    `temp` int(6) NOT NULL,
    `kiln` varchar(10) NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  </code></pre>
  
  <p><strong>error</strong></p>
  
  <pre><code>#1442 - Can't update table 'today_plan' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. 
  </code></pre>
  
  <p>thank you very much</p>