DELIMITER $$ DROP TRIGGER IF EXISTS `accounts`$$ CREATE TRIGGER `accounts` BEFORE INSERT ON `accounts` FOR EACH ROW BEGIN DECLARE last_runningcredit DOUBLE; DECLARE last_runningdebit DOUBLE; DECLARE last_accountruncredit DOUBLE; DECLARE last_accountrundebit DOUBLE; DECLARE tot_rows INTEGER; SELECT runningcredit, runningdebit, id INTO last_runningcredit, last_runningdebit, tot_rows FROM accounts WHERE entityid = NEW.entityid AND account = NEW.account ORDER BY id DESC LIMIT 1; IF tot_rows > 0 THEN SET new.runningcredit = last_runningcredit + new.credit; SET new.runningdebit = last_runningdebit + new.debit; ELSE SET new.runningcredit = new.credit; SET new.runningdebit = new.debit; END IF; IF new.account = 1 THEN IF new.credit > 0 THEN UPDATE tillclients SET b_current = b_current - new.credit WHERE id = new.entityid; UPDATE tillclients SET w_current = w_current - new.credit WHERE id = new.entityid; ELSE UPDATE tillclients SET b_current = b_current + new.debit WHERE id = new.entityid; UPDATE tillclients SET w_current = w_current + new.debit WHERE id = new.entityid; END IF; ELSE IF new.credit > 0 THEN UPDATE suppliers SET b_current = b_current + new.credit WHERE id = new.entityid; UPDATE suppliers SET w_current = w_current + new.credit WHERE id = new.entityid; ELSE UPDATE suppliers SET b_current = b_current - new.debit WHERE id = new.entityid; UPDATE suppliers SET w_current = w_current - new.debit WHERE id = new.entityid; END IF; END IF; /***************/ /*** ACCOUNT ***/ SELECT accountruncredit, accountrundebit, id INTO last_accountruncredit, last_accountrundebit, tot_rows FROM accounts WHERE account = NEW.account ORDER BY id DESC LIMIT 1; IF tot_rows > 0 THEN SET new.accountruncredit = last_accountruncredit + new.credit; SET new.accountrundebit = last_accountrundebit + new.debit; ELSE SET new.accountruncredit = new.credit; SET new.accountrundebit = new.debit; END IF; /**** UPDATE BALANCE ****/ SELECT COUNT(*) INTO tot_rows FROM account_balance_entity WHERE entityid = NEW.entityid AND account = NEW.account; IF tot_rows > 0 THEN UPDATE account_balance_entity SET entity_debit = new.runningdebit, entity_credit = new.runningcredit WHERE entityid= new.entityid AND account = new.account; UPDATE account_balance SET account_debit = new.accountrundebit, account_credit = new.accountruncredit WHERE account = new.account; ELSE INSERT INTO account_balance_entity ( entity_debit, entity_credit, entityid, account ) VALUES ( new.runningdebit, new.runningcredit, new.entityid, new.account ); SELECT COUNT(*) INTO tot_rows FROM account_balance WHERE account = NEW.account; IF tot_rows > 0 THEN UPDATE account_balance SET account_debit = new.accountrundebit, account_credit = new.accountruncredit WHERE account = new.account; ELSE INSERT INTO account_balance ( account_debit, account_credit, account ) VALUES ( new.accountrundebit, new.accountruncredit, new.account ); END IF; END IF; END; $$ DELIMITER ;