Is it possible on insert into a voucher table to update the amounts into the ledger table

Question

Most answers focus on setting a flag or inserting a row in another table my but the task I am trying to accomplish is a bit different.

I have a voucher table with the following structure

+-------------------------+---------------+
| Field                   | Type          |
+-------------------------+---------------+
| journalVoucherId        | int(11)       |
| journalVoucherCode      | varchar(25)   |
| journalVoucherNarration | varchar(99)   |
| ledgerType              | varchar(18)   |
| ledgerCode              | varchar(36)   |
| subLedgerCode1          | varchar(36)   |
| subLedgerCode2          | varchar(36)   |
| subLedgerCode3          | varchar(36)   |
| subLedgerCode4          | varchar(36)   |
| debitCurrency           | decimal(18,4) |
| creditCurrency          | decimal(18,4) |
| userId                  | varchar(99)   |
| entryDate               | varchar(99)   |
+-------------------------+---------------+

Records get inserted in this table. Whenever a record gets inserted here the values in the columns debitCurrency and creditCurrency needs to be picked up and ledger table should be updated by summing up the existing values of drBalanceLcy and crBalanceLcy with it. (Of course the ledger codes and subledger codes should match) The structure of the ledger table is as follows.

+----------------+---------------+
| Field          | Type          |
+----------------+---------------+
| ledgerId       | int(11)       |
| ledgerType     | varchar(18)   |
| ledgerCode     | varchar(36)   |
| subLedgerCode1 | varchar(36)   |
| subLedgerCode2 | varchar(36)   |
| subLedgerCode3 | varchar(36)   |
| subLedgerCode4 | varchar(36)   |
| ledgerName     | varchar(99)   |
| accountLevel   | int(3)        |
| drBalanceFcy   | decimal(18,4) |
| crBalanceFcy   | decimal(18,4) |
| drBalanceLcy   | decimal(18,4) |
| crBalanceLcy   | decimal(18,4) |
| additionalType | varchar(2)    |
| date           | varchar(99)   |
+----------------+---------------+

I created a trigger as follows but nothing seems to happen

CREATE TRIGGER ab_ledger_update AFTER INSERT ON ab_journal_voucher 
FOR EACH ROW UPDATE ab_ledger SET 
drBalanceLcy = drBalanceLcy+NEW.debitCurrency, 
crBalanceLcy = crBalanceLcy+NEW.creditCurrency 
WHERE ledgerType = NEW.ledgerType 
AND ledgerCode = NEW.ledgerCode 
AND subLedgerCode1 = NEW.subLedgerCode1 
AND subLedgerCode2 = NEW.subLedgerCode2 
AND subLedgerCode3 = NEW.subLedgerCode3 
AND subLedgerCode4 = NEW.subLedgerCode4;

Not sure what is wrong.

Here are the create table statements

CREATE TABLE `ab_ledgers` (
  `ledgerId` int(11) NOT NULL AUTO_INCREMENT,
  `ledgerType` varchar(18) DEFAULT NULL,
  `ledgerCode` varchar(36) DEFAULT NULL,
  `subLedgerCode1` varchar(36) DEFAULT NULL,
  `subLedgerCode2` varchar(36) DEFAULT NULL,
  `subLedgerCode3` varchar(36) DEFAULT NULL,
  `subLedgerCode4` varchar(36) DEFAULT NULL,
  `ledgerName` varchar(99) DEFAULT NULL,
  `accountLevel` int(3) DEFAULT NULL,
  `drBalanceFcy` decimal(18,4) DEFAULT NULL,
  `crBalanceFcy` decimal(18,4) DEFAULT NULL,
  `drBalanceLcy` decimal(18,4) DEFAULT NULL,
  `crBalanceLcy` decimal(18,4) DEFAULT NULL,
  `additionalType` varchar(2) DEFAULT NULL,
  `date` varchar(99) DEFAULT NULL,
  PRIMARY KEY (`ledgerId`)
) ENGINE=InnoDB AUTO_INCREMENT=177 DEFAULT CHARSET=latin1

CREATE TABLE `ab_journal_voucher` (
  `journalVoucherId` int(11) NOT NULL AUTO_INCREMENT,
  `journalVoucherCode` varchar(25) DEFAULT NULL,
  `journalVoucherNarration` varchar(99) DEFAULT NULL,
  `ledgerType` varchar(18) DEFAULT NULL,
  `ledgerCode` varchar(36) DEFAULT NULL,
  `subLedgerCode1` varchar(36) DEFAULT NULL,
  `subLedgerCode2` varchar(36) DEFAULT NULL,
  `subLedgerCode3` varchar(36) DEFAULT NULL,
  `subLedgerCode4` varchar(36) DEFAULT NULL,
  `debitCurrency` decimal(18,4) DEFAULT NULL,
  `creditCurrency` decimal(18,4) DEFAULT NULL,
  `userId` varchar(99) DEFAULT NULL,
  `entryDate` varchar(99) DEFAULT NULL,
  PRIMARY KEY (`journalVoucherId`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=latin1

SAMPLE INSERT Statements

INSERT INTO `ab_journal_voucher` VALUES (1,'JV2019000000000000000001','Adjustment for taxes','ASSET','1101','SL2018000000000000000011',NULL,NULL,NULL,971.0000,0.0000,'4','20-12-2019 11:12:09'),(2,'JV2019000000000000000001','Adjustment for taxes','INCOME','1302','SL2018000000000000000011',NULL,NULL,NULL,0.0000,900.0000,'4','20-12-2019 11:12:09'),(3,'JV2019000000000000000001','Adjustment for taxes','LIABILITY','1200',NULL,NULL,NULL,NULL,0.0000,71.0000,'4','20-12-2019 11:12:09'),(4,'JV2019000000000000000002','','CAPITAL','1100',NULL,NULL,NULL,NULL,1000.0000,0.0000,'4','26-12-2019 19:36:52'),(5,'JV2019000000000000000002','','ASSET','SCL',NULL,NULL,NULL,NULL,0.0000,1000.0000,'4','26-12-2019 19:36:52'),(6,'JV2019000000000000000003','','ASSET','SCL','SCL2018000000000000000046',NULL,NULL,NULL,2000.0000,0.0000,'4','26-12-2019 19:42:14'),(7,'JV2019000000000000000003','','INCOME','1302','SL2018000000000000000005',NULL,NULL,NULL,0.0000,1000.0000,'4','26-12-2019 19:42:14'),(8,'JV2019000000000000000004','','ASSET','SCL','SCL2018000000000000000046',NULL,NULL,NULL,2000.0000,0.0000,'4','26-12-2019 19:43:06'),(9,'JV2019000000000000000004','','INCOME','1302','SL2018000000000000000005',NULL,NULL,NULL,0.0000,2000.0000,'4','26-12-2019 19:43:06'),(10,'JV2019000000000000000005','','ASSET','SCL','SCL2018000000000000000047',NULL,NULL,NULL,3000.0000,0.0000,'4','26-12-2019 19:55:50'),(11,'JV2019000000000000000005','','LIABILITY','1200',NULL,NULL,NULL,NULL,0.0000,3000.0000,'4','26-12-2019 19:55:50'),(12,'JV2019000000000000000006','Reversal of Bounce Charges','ASSET','1104','SL2018000000000000000011',NULL,NULL,NULL,0.0000,400.0000,'4','27-12-2019 20:21:44'),(13,'JV2019000000000000000006','Reversal of Bounce Charges','INCOME','1304','SL2018000000000000000011',NULL,NULL,NULL,400.0000,0.0000,'4','27-12-2019 20:21:44'),(16,'JV2019000000000000000007','','ASSET','1104','SL2018000000000000000011',NULL,NULL,NULL,1001.0000,0.0000,'4','28-12-2019 11:00:22'),(17,'JV2019000000000000000007','','INCOME','1304','SL2018000000000000000011',NULL,NULL,NULL,0.0000,1000.0000,'4','28-12-2019 11:00:22'),(18,'JV2019000000000000000008','Bounce Charges accrual','ASSET','1104','SL2018000000000000000011',NULL,NULL,NULL,1000.0000,0.0000,'4','28-12-2019 12:36:28'),(19,'JV2019000000000000000008','Bounce Charges accrual','INCOME','1304','SL2018000000000000000011',NULL,NULL,NULL,0.0000,1000.0000,'4','28-12-2019 12:36:28');

INSERT INTO `ab_ledgers` VALUES (1,'CAPITAL','1100',NULL,NULL,NULL,NULL,'SHARE HOLDERS FUND',1,0.0000,0.0000,0.0000,0.0000,NULL,NULL),(2,'ASSET','1103',NULL,NULL,NULL,NULL,'BANK',1,3461049.5000,107702496.0000,3461049.5000,107702496.0000,NULL,NULL),(3,'ASSET','1103','HDFC',NULL,NULL,NULL,'BANK HDFC',2,0.0000,0.0000,0.0000,0.0000,NULL,NULL),(4,'ASSET','1103','HDFC','5200653630',NULL,NULL,'BANK HDFC 5200653630',3,28378.3696,24904500.0000,28378.3696,24904500.0000,NULL,NULL),(5,'ASSET','1103','CITY UNION BANK LIMITED',NULL,NULL,NULL,'BANK CITY UNION BANK LIMITED',2,998820.7700,9502000.0000,998820.7700,9502000.0000,NULL,NULL),(6,'ASSET','1103','CITY UNION BANK LIMITED','2110065363',NULL,NULL,'BANK CITY UNION BANK LIMITED 2110065363',3,998820.7700,9502000.0000,998820.7700,9502000.0000,NULL,NULL),(7,'ASSET','1103','UNION BANK OF INDIA',NULL,NULL,NULL,'BANK UNION BANK OF INDIA',2,490000.0000,6370000.0000,490000.0000,6370000.0000,NULL,NULL),(8,'ASSET','1103','UNION BANK OF INDIA','4110065363',NULL,NULL,'BANK UNION BANK OF INDIA 4110065363',3,490000.0000,6370000.0000,490000.0000,6370000.0000,NULL,NULL),(9,'ASSET','1105',NULL,NULL,NULL,NULL,'CASH',1,0.0000,0.0000,0.0000,0.0000,NULL,NULL);

Lennart - Slava Ukraini · Accepted Answer · 2019-12-29 14:22:52Z

Some of the columns in the WHERE clause may contain NULL, anything compared with NULL (unknown) results in NULL as a result, which is different from TRUE, so the predicate fails. Testing your trigger with rows that match shows that the trigger is working. A Fiddle with your setup

If you want to treat null as don't care, you can use COALESCE in the following way:

CREATE TRIGGER ab_ledger_update AFTER INSERT ON ab_journal_voucher 
FOR EACH ROW 
    UPDATE ab_ledgers x
        SET drBalanceLcy = drBalanceLcy+NEW.debitCurrency
          , crBalanceLcy = crBalanceLcy+NEW.creditCurrency 
    WHERE COALESCE(x.ledgerType, NEW.ledgerType, '') 
        = COALESCE(NEW.ledgerType, x.ledgerType, '')
      AND COALESCE(x.ledgerCode, NEW.ledgerCode, '')
        = COALESCE(NEW.ledgerCode, x.ledgerCode, '') 
      ...

I.e. if x.ledgerType is null it is mapped to NEW.ledgertype and vice versa. If both columns are null they are mapped to '' (assuming this is not a value that is part of the domain for ledgerType)

Stack Exchange Network

Is it possible on insert into a voucher table to update the amounts into the ledger table

1 Answer 1

Your Answer

Not the answer you're looking for? Browse other questions tagged
mysql
trigger
or ask your own question.

Hot Network Questions

Is it possible on insert into a voucher table to update the amounts into the ledger table

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged mysqltrigger or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
mysql
trigger
or ask your own question.