{"id":2027,"date":"2021-07-28T12:49:06","date_gmt":"2021-07-28T07:19:06","guid":{"rendered":"https:\/\/selfimagination.in\/tips\/?p=2027"},"modified":"2021-10-21T22:38:16","modified_gmt":"2021-10-21T17:08:16","slug":"update-stock-balance-using-trigger","status":"publish","type":"post","link":"https:\/\/selfimagination.in\/tips\/update-stock-balance-using-trigger\/","title":{"rendered":"Update Stock\/Balance Using Trigger"},"content":{"rendered":"\n<p class=\"wp-block-zozuk-wphindi\">\u091c\u092c \u092d\u0940 \u0939\u092e \u0915\u094b\u0908 \u092d\u0940 Business Application \u092c\u0928\u093e\u0924\u0947 \u0939\u0948\u0902 \u0924\u094b \u0939\u092e\u0947\u0902 Stock \u0915\u093e Balance \u0928\u093f\u0915\u0932\u0928\u093e \u0939\u094b\u0924\u093e \u0939\u0948\u0902 , \u0939\u092e\u093e\u0930\u0947 \u092a\u093e\u0938 Opening \u0939\u094b\u0924\u093e \u0939\u0948\u0902 \u0914\u0930 \u091c\u094b \u092d\u0940 Purchase \/ Sales \u0914\u0930 In \/ Out \u0939\u094b\u0924\u093e \u0939\u0948\u0902 \u0909\u0938\u0915\u094b plus\/Minus \u0915\u0930\u0928\u0947 \u0915\u0947 \u092c\u093e\u0926 \u0939\u092e\u0947\u0902 Closing \u0928\u093f\u0915\u0932\u0928\u093e \u0939\u094b\u0924\u093e \u0939\u0948\u0902 \u0964<\/p>\n\n\n\n<p class=\"wp-block-zozuk-wphindi\">\u0907\u0938 \u0915\u093e\u0930\u094d\u092f \u0915\u094b \u0905\u0927\u093f\u0915\u0924\u0930 \u0939\u092e SQL \u0932\u093f\u0916 \u0915\u0930 \u0915\u0930\u0924\u0947 \u0939\u0948\u0902, \u0935\u0948\u0938\u0947 SQL \u0932\u093f\u0916 \u0915\u0930 \u0915\u0930\u0928\u093e \u092c\u0939\u0941\u0924 \u0906\u0938\u093e\u0928 \u0939\u0948\u0902 \u092a\u0930\u0928\u094d\u0924\u0941 \u091c\u092c Data \u091c\u094d\u092f\u093e\u0926\u093e \u0939\u094b\u0924\u093e \u0939\u0948\u0902 \u0924\u094b SQL \u0915\u093e performance \u092d\u0940 Slow \u0939\u094b\u0928\u0947 \u0932\u0917\u0924\u093e \u0939\u0948\u0902 , \u092f\u0926\u093f \u0906\u092a \u091a\u093e\u0939\u0924\u0947 \u0939\u0948\u0902 \u0915\u0940 Performance Slow \u0939\u0941\u090f \u092c\u0917\u0948\u0930 \u0939\u0940 \u0939\u0940 \u0906\u092a Closing Calculation \u0915\u0930 \u092a\u093e\u090f \u0924\u094b \u0907\u0938\u0915\u093e \u0938\u092c\u0938\u0947 Best \u0924\u0930\u0940\u0915\u093e \u0939\u0948\u0902 \u0915\u0940 \u0906\u092a \u0907\u0938\u0947 Trigger \u0938\u0947 Control \u0915\u0930\u0947 <\/p>\n\n\n\n<p class=\"wp-block-zozuk-wphindi\">\u092f\u0939 \u092a\u0930 \u091c\u094b logic \u0939\u0948\u0902 \u0909\u0938\u0938\u0947 \u0906\u092a \u0915\u093f\u0938\u0940 \u092d\u0940 \u0921\u0947\u091f\u093e\u092c\u0947\u0938 \u092e\u0947\u0902 Trigger \u092c\u0928\u093e \u0938\u0915\u0924\u0947 \u0939\u0948\u0902 , \u092f\u0939\u093e\u0901 \u092a\u0930 Example \u0915\u0947 \u0932\u093f\u090f \u0939\u092e MySQL \u092e\u0947\u0902 \u092c\u0928\u093e \u0915\u0930 \u0926\u093f\u0916\u093e \u0930\u0939\u0947 \u0939\u0948\u0902 \u0964<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Advantage of Trigger<\/h2>\n\n\n\n<ul class=\"wp-block-list\"><li>Trigger \u0938\u0947 Closing Calculate \u0915\u0930\u0928\u0947 \u0938\u0947 \u0939\u092e\u0947\u0902 \u0915\u093f\u0938\u0940 \u092d\u0940 \u092a\u094d\u0930\u0915\u093e\u0930 \u0915\u0940 Calculation \u0915\u0940 SQL execute \u0928\u0939\u0940\u0902 \u0915\u0930\u0928\u093e \u0939\u094b\u0924\u0940<\/li><li>Trigger \u0938\u0947 Closing Calculate \u092c\u0939\u0941\u0924 \u0939\u0940 Fast \u0939\u094b\u0924\u093e \u0939\u0948\u0902<\/li><li>\u0939\u092e\u093e\u0930\u0947 \u092a\u093e\u0938 \u0939\u092e\u0947\u0936\u093e Live Data Store \u0939\u094b\u0924\u093e \u0939\u0948\u0902 \u091c\u094b \u0915\u093f\u0938\u0940 \u092d\u0940 \u092a\u094d\u0930\u0915\u093e\u0930 \u0915\u0947 Validation \u0932\u0917\u093e\u0928\u0947 \u092e\u0947\u0902 \u0939\u092e\u0947\u0902 \u0939\u0947\u0932\u094d\u092a \u0915\u0930\u0924\u0947 \u0939\u0948\u0902 \u0964<\/li><\/ul>\n\n\n\n<p class=\"wp-block-zozuk-wphindi\">\u0906\u091c \u0915\u0940 \u0907\u0938 Example \u092e\u0947\u0902 \u092e\u0948\u0902 \u0906\u092a\u0915\u094b \u092c\u0924\u093e\u090a\u0902\u0917\u093e \u0915\u0940 \u0915\u093f\u0938 \u0924\u0930\u0939 \u0906\u092a Trigger \u0938\u0947 \u090f\u0915 \u091f\u0947\u092c\u0932 \u092e\u0947\u0902 Balance Update \u0915\u0930\u093e \u0938\u0915\u0924\u0947 \u0939\u0948\u0902 \u0964<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Example For Update Item Balance in Item Table<\/h2>\n\n\n\n<p class=\"wp-block-zozuk-wphindi\">\u0939\u092e \u092f\u0939 \u092e\u093e\u0928 \u0915\u0930 \u0915\u093e\u0930\u094d\u092f \u0915\u0930 \u0930\u0939\u0947 \u0939\u0948\u0902 \u0915\u0940 \u0939\u092e\u093e\u0930\u0947 \u092a\u093e\u0938 \u090f\u0915 Item Table \u0939\u0948\u0902 \u0914\u0930 \u090f\u0915 Transaction Table \u0939\u0948\u0902 , \u0939\u092e\u0947\u0902 Item table me \u0938\u092d\u0940 transaction \u0915\u0940 summary Update \u0915\u0930\u0928\u093e \u0939\u0948\u0902 \u0914\u0930 Closing Calculate \u0915\u0930\u0928\u093e \u0939\u0948\u0902 \u0964<\/p>\n\n\n\n<p class=\"wp-block-zozuk-wphindi\"><a href=\"https:\/\/youtu.be\/AlcXpP7hZdA\" target=\"_blank\" rel=\"noopener\">\u092f\u0926\u093f \u0906\u092a \u0907\u0938 Example \u0915\u094b Video \u092e\u0947\u0902 \u0926\u0947\u0916\u0928\u093e \u091a\u093e\u0939\u0924\u0947 \u0939\u0948\u0902 \u0924\u094b \u092f\u0939 \u0935\u0940\u0921\u093f\u092f\u094b \u0926\u0947\u0916 \u0938\u0915\u0924\u0947 \u0939\u0948\u0902 \u0964<\/a><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">List of Tables &amp; Triggers<\/h2>\n\n\n\n<ul class=\"wp-block-list\"><li><strong>Item Table :<\/strong> ( \u091c\u093f\u0938\u092e\u0947 Item_id, item_nm, Op_qty, pur_qty, Sale_qty, Cl_qty \u0906\u0926\u093f columns \u0939\u094b\u0902\u0917\u0947 )<\/li><li><strong>Transaction Table :<\/strong> \u091c\u093f\u0938\u092e\u0947 trans_id, trans_dt, item_id, pur_qty, sale_qty \u092f\u0939 Required Column \u0939\u0948\u0902 \u0906\u092a \u0906\u0917\u0947 Rate,Amount \u0906\u0926\u093f \u092d\u0940 \u0932\u0947 \u0938\u0915\u0924\u0947 \u0939\u0948\u0902 \u0964<\/li><li><strong>Before Trigger :<\/strong> \u0939\u092e\u0947\u0902 \u092f\u0939\u093e\u0901 \u092a\u0930 Before Insert \u0914\u0930 Before Update Trigger Master Table \u092a\u0930 \u092c\u0928\u093e\u0928\u0947 \u0939\u094b\u0902\u0917\u0947 \u091c\u094b \u0915\u0940 Closing Stock \u0915\u094b \u0905\u092a\u0921\u0947\u091f \u0915\u0930\u0924\u0947 \u0930\u0939\u0947\u0902\u0917\u0947<\/li><li><strong>After Trigger :<\/strong> \u0939\u092e\u0947\u0902 \u092f\u0939\u093e\u0901 \u092a\u0930 After \u0907\u0928\u094d\u0938\u0930\u094d\u091f, After \u0905\u092a\u0921\u0947\u091f, After Delete trigger Transaction \u091f\u0947\u092c\u0932 \u092a\u0930 \u092c\u0928\u093e\u0928\u0947 \u0939\u094b\u0902\u0917\u0947 \u091c\u094b \u0915\u0940 Transaction \u0915\u0947 \u0921\u093e\u091f\u093e \u0915\u094b Master \u091f\u0947\u092c\u0932 \u092e\u0947\u0902 \u0905\u092a\u0921\u0947\u091f \u0915\u0930\u0924\u0947 \u0930\u0939\u0947\u0902\u0917\u0947<\/li><\/ul>\n\n\n\n<p class=\"wp-block-zozuk-wphindi\"><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">CREATE TABLE IF NOT EXISTS `mst_item` (\n  `item_id` int(6) NOT NULL AUTO_INCREMENT,\n  `item_nm` varchar(40) NOT NULL,\n  `op_qty` decimal(9,2) NOT NULL DEFAULT 0.00,\n  `pur_qty` decimal(9,2) NOT NULL DEFAULT 0.00,\n  `sal_qty` decimal(9,2) NOT NULL DEFAULT 0.00,\n  `cl_qty` decimal(9,2) NOT NULL DEFAULT 0.00,\n  PRIMARY KEY (`item_id`)\n);\n\n\n\/\/Before Insert Trigger on Table mst_item\n\nCREATE TRIGGER `mst_item_bi` BEFORE INSERT ON `mst_item`\n FOR EACH ROW BEGIN\n\nset new.cl_qty = new.op_qty + new.pur_qty - new.sal_qty;\n\nEND\n\n\n\n\/\/Before Update Trigger on Table mst_item\nCREATE TRIGGER `mst_item_bu` BEFORE UPDATE ON `mst_item`\n FOR EACH ROW BEGIN\n\nset new.cl_qty = new.op_qty + new.pur_qty - new.sal_qty;\n\nEND\n\n\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-zozuk-wphindi\">Item Master \u092a\u0930 \u0939\u092e \u0915\u0947\u0935\u0932 Before Trigger \u0932\u093f\u0916\u0947 \u0939\u0948\u0902 \u0915\u094d\u092f\u0941\u0915\u0940 \u0939\u092e\u0947\u0902 \u091c\u094b \u092d\u0940 Column Update \u0915\u0930\u0928\u093e \u0925\u093e \u0935\u0939 \u0909\u0938\u0940 \u091f\u0947\u092c\u0932 \u092e\u0947\u0902 \u0925\u093e , \u092f\u093e\u0928\u093f \u0939\u092e \u0915\u0939 \u0938\u0915\u0924\u0947 \u0939\u0948\u0902 \u0915\u0940 \u091c\u092c \u0939\u092e\u0947\u0902 Same Table \u092a\u0930 \u0939\u0940 \u0905\u092a\u0921\u0947\u091f \u0915\u0930\u0928\u093e \u0939\u094b \u0924\u092c \u0939\u092e Before \u0915\u093e \u0909\u092a\u092f\u094b\u0917 \u0915\u0930\u0924\u0947 \u0939\u0948\u0902 ( \u0935\u0948\u0938\u0947 \u0910\u0938\u093e \u0915\u094b\u0908 \u0928\u093f\u092f\u092e \u0928\u0939\u0940\u0902 \u0939\u0948\u0902 \u0939\u092e \u092f\u0939\u093e\u0901 \u0938\u0947 \u0926\u0942\u0938\u0930\u0940 \u091f\u0947\u092c\u0932 \u092e\u0947\u0902 \u092d\u0940 Insert\u0915\u0930 \u0938\u0915\u0924\u0947 \u0939\u0948\u0902 )<\/p>\n\n\n\n<p class=\"wp-block-zozuk-wphindi\">\u0905\u092d\u0940 \u0935\u093e\u0932\u0947 Case \u092e\u0947\u0902 Same Row \u092a\u0930 \u0939\u0940 Update \u0939\u094b \u0930\u0939\u093e \u0939\u0948\u0902 \u0907\u0938\u0932\u093f\u090f Insert \u0914\u0930 Update \u0915\u0947 Trigger \u092e\u0947\u0902 Same \u0939\u0940 Coding \u0932\u093f\u0916\u0940 \u0939\u0948\u0902 \u0964<\/p>\n\n\n\n<p class=\"wp-block-zozuk-wphindi\">Trigger \u0915\u094b Execute \u0915\u0930\u0928\u0947 \u0938\u0947 \u092a\u0939\u0932\u0947 Delimiter \u092e\u0947\u0902 $ \u0938\u0947\u091f \u0915\u0930\u0947 <\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">CREATE TABLE IF NOT EXISTS `trn_item` (\n  `trans_id` int(9) NOT NULL AUTO_INCREMENT,\n  `trans_dt` timestamp NOT NULL DEFAULT current_timestamp(),\n  `item_id` int(9) NOT NULL,\n  `pur_qty` decimal(9,2) NOT NULL DEFAULT 0.00,\n  `sal_qty` decimal(9,2) NOT NULL DEFAULT 0.00,\n  PRIMARY KEY (`trans_id`)\n);\n\n\n\/\/After Insert Trigger on Table trn_item\n\nCREATE TRIGGER `trn_item_ai` AFTER INSERT ON `trn_item`\n FOR EACH ROW BEGIN\n\n\n update mst_item \n set pur_qty = pur_qty + new.pur_qty,\n sal_qty = sal_qty + new.sal_qty\n where item_id = new.item_id;\n\n\nEND\n\n\n\n\/\/After Update Trigger on Table trn_item\n\nCREATE TRIGGER `trn_item_au` AFTER UPDATE ON `trn_item`\nFOR EACH ROW BEGIN\n\n update mst_item \n set pur_qty = pur_qty - old.pur_qty,\n sal_qty = sal_qty - old.sal_qty\n where item_id = old.item_id;\n\n\n update mst_item \n set pur_qty = pur_qty + new.pur_qty,\n sal_qty = sal_qty + new.sal_qty\n where item_id = new.item_id;\n\nEND\n\n\n\n\/\/After Delete Trigger on Table trn_item\n\nCREATE TRIGGER `trn_item_ad` AFTER DELETE ON `trn_item`\nFOR EACH ROW BEGIN\n\n update mst_item \n set pur_qty = pur_qty - old.pur_qty,\n sal_qty = sal_qty - old.sal_qty\n where item_id = old.item_id;\n\nEND\n\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-zozuk-wphindi\">Transaction table \u0938\u0947 \u0921\u093e\u091f\u093e Master \u092e\u0947\u0902 update \u0915\u0930\u0928\u093e \u0939\u0948\u0902 \u0914\u0930 Update \u091c\u092c \u0939\u0940 \u0915\u0930\u0928\u093e \u0939\u094b \u0924\u092c Transaction Table \u092e\u0947\u0902 Data Store \u0939\u094b \u091c\u093e\u092f\u0947 , \u0907\u0938\u0932\u093f\u090f \u0939\u092e\u0928\u0947 \u092f\u0939\u093e\u0901 \u092a\u0930 After Insert\/Update\/Delete Trigger \u0932\u093f\u0916\u0947 \u0939\u0948 \u0964<\/p>\n\n\n\n<p class=\"wp-block-zozuk-wphindi\">\u091c\u092c \u092d\u0940 Trigger \u0932\u093f\u0916\u0947 \u0927\u094d\u092f\u093e\u0928 \u0930\u0916\u0947 \u0915\u0940 \u0939\u092e\u0947\u0902 OLD or NEW \u0915\u094b Focus \u0915\u0930\u0928\u0947 \u0915\u0947 \u092c\u093e\u0926 \u0939\u0940 Trigger \u0932\u093f\u0916\u0928\u093e \u0939\u0948\u0902 \u092f\u0926\u093f \u0926\u0947\u0916\u093e \u091c\u093e\u092f\u0947 \u0924\u094b \u0939\u092e\u0947\u0902 \u0915\u0947\u0935\u0932 2 \u0939\u0940 SQL \u0932\u093f\u0916\u0928\u093e \u0939\u094b\u0924\u0940 \u0939\u0948\u0902 \u090f\u0915 OLD \u0915\u0947 \u0932\u093f\u090f \u0914\u0930 \u090f\u0915 NEW \u0915\u0947 \u0932\u093f\u090f \u0905\u092c \u092f\u0939 \u0926\u094b SQL \u0924\u0940\u0928\u094b Trigger \u092a\u0930 \u0932\u093f\u0916 \u0926\u0940 \u091c\u093e\u0924\u0940 \u0939\u0948\u0902 \u0964<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Insert \u092a\u0930 \u0915\u0947\u0935\u0932 NEW \u0935\u093e\u0932\u0940 SQL \u0932\u093f\u0916\u0940 \u091c\u093e\u0924\u0940 \u0939\u0948\u0902<\/li><li>Update \u092a\u0930 OLD \u0914\u0930 NEW \u0926\u094b\u0928\u094b\u0902 SQL \u0932\u093f\u0916\u0940 \u091c\u093e\u0924\u0940 \u0939\u0948\u0902<\/li><li>Delete \u092a\u0930 \u0915\u0947\u0935\u0932 OLD \u0935\u093e\u0932\u0940 SQL \u0932\u093f\u0916\u0940 \u091c\u093e\u0924\u0940 \u0939\u0948\u0902 \u0964<\/li><\/ul>\n\n\n\n<p class=\"wp-block-zozuk-wphindi\">\u092f\u0939 Trigger \u0932\u093f\u0916\u0928\u0947 \u0915\u0940 \u092c\u0939\u0941\u0924 \u0939\u0940 \u0906\u0938\u093e\u0928 Step \u0939\u0948\u0902 \u0907\u0938 Process \u0938\u0947 \u0906\u092a \u092c\u0921\u093c\u0940 \u0938\u0947 \u092c\u0921\u093c\u0940 SQL \u0932\u093f\u0916 \u0938\u0915\u0924\u0947 \u0939\u0948\u0902 \u0964 \u0906\u092a\u0915\u094b \u0915\u0939\u0940 \u092a\u0930 MySQL \u091f\u094d\u0930\u093f\u0917\u0930 \u0938\u0947 \u0938\u092e\u094d\u092c\u0902\u0927\u093f\u0924 \u0915\u0941\u091b \u0938\u092e\u091d\u0928\u093e \u0939\u094b \u092f\u093e \u0915\u0939\u0940 \u092a\u0930 \u092d\u0940 \u0915\u094b\u0908 Doubt \u0939\u094b \u0924\u094b \u0906\u092a Comment \u0915\u0930 \u0938\u0915\u0924\u0947 \u0939\u094b<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Advantage of Trigger Trigger \u0938\u0947 Closing Calculate \u0915\u0930\u0928\u0947 \u0938\u0947 \u0939\u092e\u0947\u0902 \u0915\u093f\u0938\u0940 \u092d\u0940 \u092a\u094d\u0930\u0915\u093e\u0930 \u0915\u0940 Calculation \u0915\u0940 SQL execute \u0928\u0939\u0940\u0902 \u0915\u0930\u0928\u093e \u0939\u094b\u0924\u0940 Trigger \u0938\u0947 Closing Calculate \u092c\u0939\u0941\u0924 \u0939\u0940 Fast \u0939\u094b\u0924\u093e \u0939\u0948\u0902 \u0939\u092e\u093e\u0930\u0947 \u092a\u093e\u0938 \u0939\u092e\u0947\u0936\u093e Live Data Store \u0939\u094b\u0924\u093e \u0939\u0948\u0902 \u091c\u094b \u0915\u093f\u0938\u0940 \u092d\u0940 \u092a\u094d\u0930\u0915\u093e\u0930 \u0915\u0947 Validation \u0932\u0917\u093e\u0928\u0947 \u092e\u0947\u0902 \u0939\u092e\u0947\u0902 \u0939\u0947\u0932\u094d\u092a \u0915\u0930\u0924\u0947 \u0939\u0948\u0902 \u0964 Example For Update Item [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":2046,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[5],"tags":[],"class_list":["post-2027","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mysql"],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"https:\/\/i0.wp.com\/selfimagination.in\/tips\/wp-content\/uploads\/2021\/07\/update-stock-using-trigger.jpg?fit=1200%2C628&ssl=1","_links":{"self":[{"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/posts\/2027","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/comments?post=2027"}],"version-history":[{"count":23,"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/posts\/2027\/revisions"}],"predecessor-version":[{"id":4528,"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/posts\/2027\/revisions\/4528"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/media\/2046"}],"wp:attachment":[{"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/media?parent=2027"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/categories?post=2027"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/selfimagination.in\/tips\/wp-json\/wp\/v2\/tags?post=2027"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}