Narasimha Maiya Narasimha Maiya - 4 months ago 9
MySQL Question

Update a column after every insert

I have a table structure given below.

CREATE TABLE `sample` (
`PK_ID` varchar(10) NOT NULL,
`RQ_ID` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`BID_VAL` double NOT NULL DEFAULT '0',
`max_bid` double NOT NULL,
`Last_updated` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


The column
max_bid
has to be auto generated which contains
max(BID_VAL) group by RQ_ID


I need a trigger which will update
max_bid
after
insert


Thank you.

Answer

You can use procedure to that like this :

DELIMITER ||
CREATE PROCEDURE update_max ( 
 IN PK_ID_val varchar(10),
 IN RQ_ID_VAL varchar(10) ,
 IN BID_VAL_VAL double ,
 IN Last_updated_VAL datetime 
 )
BEGIN
DECLARE calcul_max_bid double;
 insert into sample 
        (PK_ID,RQ_ID,BID_VAL,MAX_BID,Last_updated)
  values (PK_ID_VAL,RQ_ID_VAL,BID_VAL_VAL,0,Last_updated_VAL);
  SELECT 
    max(BID_VAL) into calcul_max_bid 
    FROM sample 
    WHERE 
    RQ_ID = RQ_ID_VAL
  GROUP BY RQ_ID;
 update sample set max_bid = calcul_max_bid where RQ_ID=RQ_ID_val;
END ||
DELIMITER ;

so instead of execute insert directly call this procedure

call update_max('A','RQ_1',12.0,'2016-12-12');
Comments