The Ultimate Guide to High Availability with MariaDB

Comments - Stored Function Limitations

4 years, 5 months ago Francisco Miralha
Re: Stored Function Limitations

How can we deal with aggregate columns like "balance", for example?

I'm trying to setup triggers to keep my "balance" column consistent over time no matter what chages are made to "expected_date" or "credit" or "debit" columns.

The "formula" to balance calc on each row is:

this_balance = previousBalance() + this_credit - this_debit;

CREATE TABLE `cash_flow` (
	`row_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
	`expected_date` DATETIME NULL DEFAULT NULL,
	`description` VARCHAR(200) NULL DEFAULT '' COLLATE 'utf8_general_ci',
	`credit` DECIMAL(18,2) NULL DEFAULT '0.00',
	`debit` DECIMAL(18,2) NULL DEFAULT '0.00',
	`balance` DECIMAL(18,2) NULL DEFAULT '0.00',
	PRIMARY KEY (`row_id`) USING BTREE,
	INDEX `expected_date` (`expected_date`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
 
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.