Saving with new mysql 5.7 triggers in db

Ciarán ciarandbrowne at
Sun May 14 12:22:24 EDT 2017

Hi all,

I'm using GNUCash for home accounts for several years and I love it! Perfect
for an old time accountant! Anyway the reporting has always bothered me so
I've built an OBIEE 11g solution with mysql 5.7
I have a simple solution which only uses three tables (accounts,splits &
transactions) and it works like a charm. However building time hierarchies
in OBI requires some additional db changes in GNUCash to normalize the data

Year, month, day

ALTER TABLE gnucash.transactions
ADD post_year YEAR(4);

ALTER TABLE gnucash.transactions
ADD post_day INT(2);

ALTER TABLE gnucash.transactions
ADD post_month INT(2);

UPDATE gnucash.transactions
SET post_year = year(post_date);

UPDATE gnucash.transactions
SET post_month = month(post_date);

UPDATE gnucash.transactions
SET post_day = day(post_date);

This works great and is robust throughout saves.

Next to automate the updates I created a trigger

CREATE TRIGGER ins_transactions
AFTER INSERT ON gnucash.transactions 
 INSERT INTO gnucash.transactions
	post_year = year(post_date),
    post_month = month(post_date),
    post_day = day(post_date);

However from this point onwards GNUcash will not save when new transcations
are created.

A msgbox appears with unable to save to database. 

To resolve, I have re-save the db file overwriting the additional columns in
the transactions table.

and... back to square one.

Any ideas how to maintain new mysql triggers on save in gnucash? Is this

View this message in context:
Sent from the GnuCash - Dev mailing list archive at

More information about the gnucash-devel mailing list