MySql Trigger

Some Work with MySql Trigger :

 Table Structure 

DROP TABLE IF EXISTS `SchemaName`.`prd_cur_balance`;  
 CREATE TABLE `SchemaName`.`prd_cur_balance` (  
  `id` int(10) unsigned NOT NULL auto_increment,  
  `br_code` varchar(45) NOT NULL default '0',  
  `prd_id` int(10) unsigned NOT NULL default '0',  
  `rate` float NOT NULL default '0',  
  `bqty` float NOT NULL default '0',  
  PRIMARY KEY (`id`)  
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;  
 insert into prd_cur_balance(br_code,prd_id,rate,bqty)  
 SELECT c.br_code,c.prd_id,c.rate,(c.bqty-IFNULL(d.bqty,0)) bqty  
 FROM  
 (SELECT c.br_code,prd_id,rate,sum(qty) bqty FROM challan c, challan_details d where c.id=d.chn_id group by c.br_code,d.prd_id,d.rate) c left outer join  
 (SELECT c.br_code,prd_id,rate,sum(qty) bqty FROM comsumed c, consumed_details d where c.id=d.cons_id group by c.br_code,prd_id,rate) d  
 on c.br_code=d.br_code and c.prd_id=d.prd_id and c.rate=d.rate;  



 DROP TRIGGER trigger_cha_update;  
 DELIMITER $$  
 CREATE TRIGGER trigger_cha_update BEFORE UPDATE ON challan_details  
 FOR EACH ROW  
 BEGIN  
    IF NOT EXISTS(select * from prd_cur_balance where br_code=(select br_code from challan c where c.id=new.chn_id) and prd_id=NEW.prd_id and rate=NEW.rate) THEN  
     insert into prd_cur_balance(br_code,prd_id,rate,bqty)  
     select br_code,new.prd_id,new.rate,new.qty from challan c where c.id=new.chn_id;  
    ELSE  
     UPDATE prd_cur_balance set bqty=bqty-IFNULL(OLD.qty,0) where br_code=(select br_code from challan c where c.id=old.chn_id) and prd_id=old.prd_id and rate=old.rate;  
     UPDATE prd_cur_balance set bqty=bqty+new.qty where br_code=(select br_code from challan c where c.id=new.chn_id) and prd_id=NEW.prd_id and rate=NEW.rate ;  
     delete from prd_cur_balance where bqty=0;  
    END IF;  
 END $$  
 DELIMITER ;  



 DROP TRIGGER trigger_cha_delete;  
 DELIMITER $$  
 CREATE TRIGGER trigger_cha_delete BEFORE DELETE ON challan_details  
 FOR EACH ROW  
 BEGIN  
   UPDATE prd_cur_balance set bqty=bqty-IFNULL(OLD.qty,0) where br_code=(select br_code from challan c where c.id=old.chn_id) and prd_id=old.prd_id and rate=old.rate ;  
   delete from prd_cur_balance where bqty=0;  
 END $$  
 DELIMITER ;  



 DROP TRIGGER trigger_cha_insert;  
 DELIMITER $$  
 CREATE TRIGGER trigger_cha_insert BEFORE INSERT ON challan_details  
 FOR EACH ROW  
 BEGIN  
    IF NOT EXISTS(select * from prd_cur_balance where br_code=(select br_code from challan c where c.id=new.chn_id) and prd_id=NEW.prd_id and rate=NEW.rate) THEN  
     insert into prd_cur_balance(br_code,prd_id,rate,bqty)  
     select br_code,new.prd_id,new.rate,new.qty from challan c where c.id=new.chn_id;  
    ELSE  
     UPDATE prd_cur_balance set bqty=bqty+new.qty where br_code=(select br_code from challan c where c.id=new.chn_id) and prd_id=NEW.prd_id and rate=NEW.rate;  
    END IF;  
 END $$  
 DELIMITER ;  


 DROP TRIGGER trigger_cons_update;  
 DELIMITER $$  
 CREATE TRIGGER trigger_cons_update BEFORE UPDATE ON consumed_details  
 FOR EACH ROW  
 BEGIN  
   UPDATE prd_cur_balance set bqty=bqty+IFNULL(OLD.qty,0) where br_code=(select br_code from comsumed c where c.id=old.cons_id) and prd_id=old.prd_id and rate=old.rate;  
   UPDATE prd_cur_balance set bqty=bqty-new.qty where br_code=(select br_code from comsumed c where c.id=new.cons_id) and prd_id=NEW.prd_id and rate=NEW.rate;  
   -- delete from prd_cur_balance where bqty=0;  
 END $$  
 DELIMITER ;  


 DROP TRIGGER trigger_cons_delete;  
 DELIMITER $$  
 CREATE TRIGGER trigger_cons_delete BEFORE DELETE ON consumed_details  
 FOR EACH ROW  
 BEGIN  
   UPDATE prd_cur_balance set bqty=bqty+IFNULL(OLD.qty,0) where br_code=(select br_code from comsumed c where c.id=old.cons_id) and prd_id=old.prd_id and rate=old.rate ;  
   -- delete from prd_cur_balance where bqty=0;  
 END $$  
 DELIMITER ;  


 DROP TRIGGER trigger_cons_insert;  
 DELIMITER $$  
 CREATE TRIGGER trigger_cons_insert BEFORE INSERT ON consumed_details  
 FOR EACH ROW  
 BEGIN  
   UPDATE prd_cur_balance set bqty=bqty-IFNULL(new.qty,0) where br_code=(select br_code from comsumed c where c.id=new.cons_id) and prd_id=new.prd_id and rate=new.rate ;  
   -- delete from prd_cur_balance where bqty=0;  
 END $$  
 DELIMITER ;