CODEWITHSUNDEEP
mysqlsqldatabasetriggers
MoniXx
MoniXx

Reputation: 135

Trigger Not Calculating Sum Properly MySQL

I have a trigger that calculates the sum using a previous row and a current row and outputs the sum in balance field of the current row,The problem is that it doesnt seem to calculate the sum of the first row inserted into the table my trigger is:

delimiter $$
CREATE TRIGGER `ledger_calc` AFTER INSERT ON `sp_records`
FOR EACH ROW BEGIN
SET @PrevBal := (SELECT balance FROM ledger WHERE cmp_name = NEW.cmp_name AND inv_for = NEW.inv_for ORDER BY id DESC
LIMIT 1); 
SET @CmpName := NEW.cmp_name;
SET @InvFor := NEW.inv_for;

IF (NEW.type = 'sales') THEN
SET @Type := 'sales';

INSERT INTO ledger (id,inv_for,cmp_name,date,debit,balance)
SELECT
      TransactDet.id,
     TransactDet.inv_for,
    TransactDet.cmp_name,
      TransactDet.date,
      TransactDet.tot_amnt,
      @PrevBal :=  @PrevBal + TransactDet.tot_amnt as balance
   from 
      ( select
              Transact.id,
              Transact.date,
              Transact.tot_amnt,
              Transact.cmp_name,
              Transact.inv_for

           from
              sp_records Transact
           where Transact.type = @Type
            AND Transact.inv_for = @InvFor
            AND Transact.cmp_name = @CmpName
           order by
            Transact.id DESC
            limit 1 ) as TransactDet;
ELSE
SET @Type := 'purchase';
INSERT INTO ledger (id,inv_for,cmp_name,date,credit,balance)
SELECT
      TransactDet.id,
     TransactDet.inv_for,
    TransactDet.cmp_name,
      TransactDet.date,
      TransactDet.tot_amnt,
      @PrevBal := @PrevBal - TransactDet.tot_amnt as balance
   from 
      ( select
              Transact.id,
              Transact.date,
              Transact.tot_amnt,
                Transact.cmp_name,
              Transact.inv_for
           from
              sp_records Transact
           where Transact.type = @Type
            AND Transact.inv_for = @InvFor
            AND Transact.cmp_name = @CmpName
           order by
            Transact.id DESC
            limit 1) as TransactDet;
END IF;

END;
#

This query returned what i want.. but it doesnt seem to convert to a trigger...

SELECT
      PreAgg.id,
      PreAgg.tot_amnt,
      @PrevBal := @PrevBal + PreAgg.tot_amnt as balance
   from 
      ( select
              YT.id,
              YT.tot_amnt
           from
              sp_records YT
           order by
              YT.id ) as PreAgg,
      ( select @PrevBal := 0.00 ) as SqlVars

Upvotes: 0

Views: 162

Answers (1)

fancyPants
fancyPants

Reputation: 51888

Please have a try with this one:

delimiter $$

CREATE TRIGGER `ledger_calc` BEFORE INSERT ON `sp_records`
FOR EACH ROW
BEGIN
    SET @PrevBal : = (
            SELECT balance
            FROM ledger
            WHERE cmp_name = NEW.cmp_name
                AND inv_for = NEW.inv_for
            ORDER BY id DESC LIMIT 1
            );

    INSERT INTO ledger (id, inv_for, cmp_name, DATE, debit, balance)
    SELECT Transact.id, Transact.inv_for, Transact.cmp_name, Transact.DATE, Transact.tot_amnt, @PrevBal + Transact.tot_amnt AS balance
    FROM sp_records Transact
    WHERE Transact.type = NEW.type
        AND Transact.inv_for = NEW.inv_for
        AND Transact.cmp_name = NEW.cmp_name
    ORDER BY Transact.id DESC limit 1;
END $$

I don't know, what your table structure looks like, but you might as well forget about the trigger and just do

INSERT INTO ledger (id, inv_for, cmp_name, DATE, debit, balance)
SELECT t.id, t.inv_for, t.cmp_name, t.DATE, t.tot_amnt, l.balance + t.tot_amnt AS balance
FROM sp_records t
INNER JOIN ledger l ON t.inv_for = l.inv_for AND t.cmp_name = l.cmp_name
WHERE t.type = 'a_value'
    AND t.inv_for = 'another_value'
    AND t.cmp_name = 'yet_another_value'
ORDER BY t.id DESC limit 1;

or as a trigger

DELIMITER $$
CREATE TRIGGER `ledger_calc` AFTER INSERT ON `sp_records`
FOR EACH ROW
BEGIN
    INSERT INTO ledger (id, inv_for, cmp_name, DATE, debit, balance)
    SELECT t.id, t.inv_for, t.cmp_name, t.DATE, t.tot_amnt, l.balance + t.tot_amnt AS balance
    FROM sp_records t
    INNER JOIN ledger l ON t.inv_for = l.inv_for AND t.cmp_name = l.cmp_name
    WHERE t.type = NEW.type
        AND t.inv_for = NEW.inv_for
        AND t.cmp_name = NEW.cmp_name
    ORDER BY t.id DESC limit 1;
END $$

If all this doesn't help, sample data and desired result would be helpful, preferably on http://sqlfiddle.com

Upvotes: 1

Related Questions