Update Stock/Balance Using Trigger

जब भी हम कोई भी Business Application बनाते हैं तो हमें Stock का Balance निकलना होता हैं , हमारे पास Opening होता हैं और जो भी Purchase / Sales और In / Out होता हैं उसको plus/Minus करने के बाद हमें Closing निकलना होता हैं ।

इस कार्य को अधिकतर हम SQL लिख कर करते हैं, वैसे SQL लिख कर करना बहुत आसान हैं परन्तु जब Data ज्यादा होता हैं तो SQL का performance भी Slow होने लगता हैं , यदि आप चाहते हैं की Performance Slow हुए बगैर ही ही आप Closing Calculation कर पाए तो इसका सबसे Best तरीका हैं की आप इसे Trigger से Control करे

यह पर जो logic हैं उससे आप किसी भी डेटाबेस में Trigger बना सकते हैं , यहाँ पर Example के लिए हम MySQL में बना कर दिखा रहे हैं ।

Advantage of Trigger

• Trigger से Closing Calculate करने से हमें किसी भी प्रकार की Calculation की SQL execute नहीं करना होती
• Trigger से Closing Calculate बहुत ही Fast होता हैं
• हमारे पास हमेशा Live Data Store होता हैं जो किसी भी प्रकार के Validation लगाने में हमें हेल्प करते हैं ।

आज की इस Example में मैं आपको बताऊंगा की किस तरह आप Trigger से एक टेबल में Balance Update करा सकते हैं ।

Example For Update Item Balance in Item Table

हम यह मान कर कार्य कर रहे हैं की हमारे पास एक Item Table हैं और एक Transaction Table हैं , हमें Item table me सभी transaction की summary Update करना हैं और Closing Calculate करना हैं ।

यदि आप इस Example को Video में देखना चाहते हैं तो यह वीडियो देख सकते हैं ।

List of Tables & Triggers

• Item Table : ( जिसमे Item_id, item_nm, Op_qty, pur_qty, Sale_qty, Cl_qty आदि columns होंगे )
• Transaction Table : जिसमे trans_id, trans_dt, item_id, pur_qty, sale_qty यह Required Column हैं आप आगे Rate,Amount आदि भी ले सकते हैं ।
• Before Trigger : हमें यहाँ पर Before Insert और Before Update Trigger Master Table पर बनाने होंगे जो की Closing Stock को अपडेट करते रहेंगे
• After Trigger : हमें यहाँ पर After इन्सर्ट, After अपडेट, After Delete trigger Transaction टेबल पर बनाने होंगे जो की Transaction के डाटा को Master टेबल में अपडेट करते रहेंगे

CREATE TABLE IF NOT EXISTS `mst_item` (
  `item_id` int(6) NOT NULL AUTO_INCREMENT,
  `item_nm` varchar(40) NOT NULL,
  `op_qty` decimal(9,2) NOT NULL DEFAULT 0.00,
  `pur_qty` decimal(9,2) NOT NULL DEFAULT 0.00,
  `sal_qty` decimal(9,2) NOT NULL DEFAULT 0.00,
  `cl_qty` decimal(9,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`item_id`)
);


//Before Insert Trigger on Table mst_item

CREATE TRIGGER `mst_item_bi` BEFORE INSERT ON `mst_item`
 FOR EACH ROW BEGIN

set new.cl_qty = new.op_qty + new.pur_qty - new.sal_qty;

END



//Before Update Trigger on Table mst_item
CREATE TRIGGER `mst_item_bu` BEFORE UPDATE ON `mst_item`
 FOR EACH ROW BEGIN

set new.cl_qty = new.op_qty + new.pur_qty - new.sal_qty;

END

Item Master पर हम केवल Before Trigger लिखे हैं क्युकी हमें जो भी Column Update करना था वह उसी टेबल में था , यानि हम कह सकते हैं की जब हमें Same Table पर ही अपडेट करना हो तब हम Before का उपयोग करते हैं ( वैसे ऐसा कोई नियम नहीं हैं हम यहाँ से दूसरी टेबल में भी Insertकर सकते हैं )

अभी वाले Case में Same Row पर ही Update हो रहा हैं इसलिए Insert और Update के Trigger में Same ही Coding लिखी हैं ।

Trigger को Execute करने से पहले Delimiter में $ सेट करे

CREATE TABLE IF NOT EXISTS `trn_item` (
  `trans_id` int(9) NOT NULL AUTO_INCREMENT,
  `trans_dt` timestamp NOT NULL DEFAULT current_timestamp(),
  `item_id` int(9) NOT NULL,
  `pur_qty` decimal(9,2) NOT NULL DEFAULT 0.00,
  `sal_qty` decimal(9,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`trans_id`)
);


//After Insert Trigger on Table trn_item

CREATE TRIGGER `trn_item_ai` AFTER INSERT ON `trn_item`
 FOR EACH ROW BEGIN


 update mst_item 
 set pur_qty = pur_qty + new.pur_qty,
 sal_qty = sal_qty + new.sal_qty
 where item_id = new.item_id;


END



//After Update Trigger on Table trn_item

CREATE TRIGGER `trn_item_au` AFTER UPDATE ON `trn_item`
FOR EACH ROW BEGIN

 update mst_item 
 set pur_qty = pur_qty - old.pur_qty,
 sal_qty = sal_qty - old.sal_qty
 where item_id = old.item_id;


 update mst_item 
 set pur_qty = pur_qty + new.pur_qty,
 sal_qty = sal_qty + new.sal_qty
 where item_id = new.item_id;

END



//After Delete Trigger on Table trn_item

CREATE TRIGGER `trn_item_ad` AFTER DELETE ON `trn_item`
FOR EACH ROW BEGIN

 update mst_item 
 set pur_qty = pur_qty - old.pur_qty,
 sal_qty = sal_qty - old.sal_qty
 where item_id = old.item_id;

END

Transaction table से डाटा Master में update करना हैं और Update जब ही करना हो तब Transaction Table में Data Store हो जाये , इसलिए हमने यहाँ पर After Insert/Update/Delete Trigger लिखे है ।

जब भी Trigger लिखे ध्यान रखे की हमें OLD or NEW को Focus करने के बाद ही Trigger लिखना हैं यदि देखा जाये तो हमें केवल 2 ही SQL लिखना होती हैं एक OLD के लिए और एक NEW के लिए अब यह दो SQL तीनो Trigger पर लिख दी जाती हैं ।

• Insert पर केवल NEW वाली SQL लिखी जाती हैं
• Update पर OLD और NEW दोनों SQL लिखी जाती हैं
• Delete पर केवल OLD वाली SQL लिखी जाती हैं ।

यह Trigger लिखने की बहुत ही आसान Step हैं इस Process से आप बड़ी से बड़ी SQL लिख सकते हैं । आपको कही पर MySQL ट्रिगर से सम्बंधित कुछ समझना हो या कही पर भी कोई Doubt हो तो आप Comment कर सकते हो