mysql TRIGGER update field after update

Question

i have 2 table today_plan and kiln_master.

after pattern and itemno inserted to today_plan then need to select matching yeild from kiln_master table and update this value to yeild field in today_plan.

i have created a trigger

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

what part is wrong with my code

today_plan

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;

kiln_master

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;

error

#1442 - Can't update table 'today_plan' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

thank you very much

Joachim Isaksson · Accepted Answer · 2013-07-26 06:05:37Z

1

You're trying to update the row after it has been inserted, isn't it better to do before so that it gets inserted with the correct values? That'd be something like;

CREATE TRIGGER update_yeild BEFORE INSERT ON today_plan
FOR EACH ROW 
  SET NEW.yeild = COALESCE((SELECT kiln_master.yeild 
                   FROM kiln_master
                   WHERE NEW.itemno = kiln_master.item  
                   AND  NEW.pattern = kiln_master.pattern
                   LIMIT 1), 0);

answered Jul 26, 2013 at 6:05

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql TRIGGER update field after update

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related