too slow


/***************************************
           upd_i00_tot
***************************************/
DROP PROCEDURE IF EXISTS upd_i00_tot;
/*peg_end*/

create procedure upd_i00_tot (IN _pno CHAR(14), IN _p03 DEC(14,5), IN _p610 INT(6), IN _p05 INT(6) )
begin
    DECLARE _posot INT;
       select IF(i15.p03 IS NULL, 0, i15.p03-i15.p04) INTO _posot
       FROM i15
       WHERE i15.p01=_p05;

       INSERT INTO i00tot
          SET nr01=peg_new_nr01('i00tot', 1), 
              pno1=substr(_pno, 1, 14), p610=_p610, p03=if(_posot*_p03 IS NULL, 0, _posot*_p03),
              remdate=curdate(), remtime=curtime(), remfdate=curdate(), remftime=curtime()
       ON DUPLICATE KEY UPDATE
          p03=p03 + if(_posot*_p03 IS NULL, 0, _posot*_p03),
          remdate=curdate(), remtime=curtime() ;
end;
/*peg_end*/


/***************************************
           upd_i00to1
***************************************/
DROP PROCEDURE IF EXISTS upd_i00to1;
/*peg_end*/

create procedure upd_i00to1 (IN _pno CHAR(14), IN _p051 INT(5), IN _p052 INT(5) )
begin

     /****************************************************
         update if old p05 or new p05 updates i00to1
      ****************************************************/
     IF (SELECT ifnull(i15.nr01, 0) FROM i15 WHERE p01 IN (_p051, _p052) && p05=1)<>0
     THEN 
          INSERT INTO i00to1
          (nr01, p01, p02, p03, p04, p05, p06, p07, p08, 
          remfdate, remftime, remdate, remtime)
          (SELECT peg_new_nr01('i00to1', 1) as nr01, 
                  i00.pno as p01, 
                  cast(ifnull(i01.p013, '01000101') as date) as p02, 
                  ifnull(i01.p014, '') as p03, 
                  ifnull(i01.p022/i01.p03, 0) as p04, 
                  ifnull(i01.p04, 0)   as p05, 
                  ifnull(i01.p010, 0)  as p06, 
                  ifnull(i01.p010a, 0) as p07, 
                  ifnull(i01.nr01, 0)  as p08, 
                  curdate() as remfdate,
                  curtime() as remftime, 
                  curdate() as remdate,
                  curtime() as remtime
           FROM i00
           LEFT JOIN i01 ON i00.pno=i01.p01
                        &&  i01.nr01= (SELECT i01.nr01 FROM i01, i15
                                       where i01.p01=i00.pno
                                          && i01.p05=i15.p01
                                          && i01.p022<>0
                                          && i01.p03<>0
                                          && i01.p012=1
                                          && i15.p05=1
                                       ORDER BY i01.p013 DESC, i01.remfdate desc, i01.remftime desc, i01.nr01 desc 
                                       limit 1) 
           WHERE i00.pno=_pno
           )
           ON DUPLICATE KEY UPDATE 
              p02=values(p02), 
              p03=values(p03), 
              p04=values(p04), 
              p05=values(p05), 
              p06=values(p06), 
              p07=values(p07), 
              p08=values(p08), 
              remdate=values(remdate), 
              remtime=values(remtime); 
     END IF;         
end;
/*peg_end*/


/**********************************
          TRIGGERS 
**********************************/
DROP TRIGGER IF EXISTS i01_ins;
/*peg_end*/

CREATE TRIGGER i01_ins AFTER INSERT ON i01
FOR EACH ROW
BEGIN 
    call upd_i00_tot(new.p01, new.p03*new.p012, new.p610, new.p05);
    call upd_i00to1(new.p01, new.p05, -1);
END;
/*peg_end*/

DROP TRIGGER IF EXISTS i01_upd;
/*peg_end*/

CREATE TRIGGER i01_upd AFTER UPDATE ON i01
FOR EACH ROW
BEGIN
  /****update total*****/
  IF old.p01<>new.p01 || 
     old.p03<>new.p03 || 
     old.p012<>new.p012 || 
     old.p610<>new.p610 || 
     old.p05<>new.p05 
     THEN
    call upd_i00_tot(old.p01, -1*old.p03*old.p012, old.p610, old.p05);
    call upd_i00_tot(new.p01, new.p03*new.p012, new.p610, new.p05);
  END IF ;

  /****update last purchase*****/
  IF old.p01<>new.p01 || 
     old.p03<>new.p03 || 
     old.p04<>new.p04 || 
     old.p010<>new.p010 || 
     old.p010a<>new.p010a || 
     old.p013<>new.p013 || 
     old.p014<>new.p014 || 
     old.p022<>new.p022 || 
     old.p012<>new.p012 || 
     old.p05<>new.p05 
  THEN
    call upd_i00to1(old.p01, old.p05, new.p05);
    IF old.p01<>new.p01 
    THEN 
        call upd_i00to1(new.p01, old.p05, new.p05);
    END IF;    
  END IF ;

END;
/*peg_end*/

DROP TRIGGER IF EXISTS i01_del;
/*peg_end*/

CREATE TRIGGER i01_del AFTER DELETE ON i01
FOR EACH ROW
BEGIN
    call upd_i00_tot(old.p01, -1*old.p03*old.p012, old.p610, old.p05);
    call upd_i00to1(old.p01, old.p05, -1);
END;
/*peg_end*/
