I converted my db as postgresql to mysql, but I can't able to convert trigger. How to change this trigger as mysql format?

Question

I made a database migration from Postgres to MySQL, now I can't able to convert a trigger to this new database engine. Postgres trigger function below:

CREATE OR REPLACE FUNCTION ins_stk_dt_in_lgr_frm_purchase_entry()
  RETURNS trigger AS
$BODY$
 declare
        stocid integer;
        rec record;
        rec1 record;
        dt date;
        begin
        dt = now()::date;
        for rec in
        select a.trnpurenthdid,c.productid,b.prodserialno as serial_no,b.prodbatchno,b.qty as qtyrecd,a.uom,b.warranty_date,b.exp_date from trnpurentdet a
        join   trnpurentdet2 b on a.trnpurentdetid = b.trnpurentdet1id
        join mstproductdet c on c.productdetid=a.productpetid
    where trnpurentdet2id = new.trnpurentdet2id
        loop
        if(rec.serial_no <> '') then
            insert into stock_leger (leger_date,io_flag,transtype,referdoctno,product_id,serialno,systemremark,qty,uom,expiry_date,warrenty_date) values (dt,1,'PE',rec.trnpurenthdid,rec.productid,rec.serial_no,'PUR_ENT',rec.qtyrecd,rec.uom,rec.exp_date,rec.warranty_date); 
        end if; 
        if(rec.prodbatchno <> '') then
            insert into stock_leger (leger_date,io_flag,transtype,referdoctno,product_id,batchno,systemremark,qty,uom,expiry_date,warrenty_date) values (dt,1,'PE',rec.trnpurenthdid,rec.productid,rec.prodbatchno,'PUR_ENT',rec.qtyrecd,rec.uom,rec.exp_date,rec.warranty_date); 
        end if;
       end loop;        
       return new;
       end;
   $BODY$;

How to change this trigger as mysql format?

Have you had a look at this useful resource? https://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html — David Aldridge, Jun 24 '17 at 14:06

score 0 · Answer 1 · answered Jun 27 '17 at 06:42

   DELIMITER //
   CREATE TRIGGER ins_stk_dt_in_lgr_frm_purchase_entry
   AFTER INSERT on trnpurentdet2 for each row BEGIN
   DECLARE done INT DEFAULT FALSE;
   DECLARE a1,b1,e1,f1 INT;
   DECLARE c1,d1 varchar(50);
   DECLARE dt,g1,h1 date;
   DECLARE rec CURSOR FOR   SELECT a.trnpurenthdid,c.productid,b.prodserialno as serial_no,b.prodbatchno,b.qty as qtyrecd,a.uom,b.warranty_date,b.exp_date from trnpurentdet a
    join   trnpurentdet2 b on a.trnpurentdetid = b.trnpurentdet1id
    join mstproductdet c on c.productdetid=a.productpetid
where trnpurentdet2id = NEW.trnpurentdet2id;

   set dt = curdate();
   OPEN rec;
   ins_loop: LOOP
  FETCH rec INTO a1, b1, c1,d1,e1,f1,g1,h1;
  IF done THEN
  LEAVE ins_loop;
  END IF;
  IF(c1 <> '') THEN
  INSERT into stock_leger (leger_date,io_flag,transtype,referdoctno,product_id,serialno,systemremark,qty,uom,expiry_date,warrenty_date) values (dt,1,'PE',a1,b1,c1,'PUR_ENT',e1,f1,g1,h1);
    ELSE
  INSERT into stock_leger (leger_date,io_flag,transtype,referdoctno,product_id,batchno,systemremark,qty,uom,expiry_date,warrenty_date) values (dt,1,'PE',a1,b1,d1,'PUR_ENT',e1,f1,g1,h1); 
  END IF;
    END LOOP;
    CLOSE rec;
    END;//
    DELIMITER;

I converted my db as postgresql to mysql, but I can't able to convert trigger. How to change this trigger as mysql format?

1 Answers1