CODEWITHSUNDEEP
mysqltriggers
9pixle
9pixle

Reputation: 556

mysql TRIGGER update field after update

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

Upvotes: 0

Views: 1055

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 181077

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);

Upvotes: 1

Related Questions