Saving with new mysql 5.7 triggers in db

Geert Janssens geert.gnucash at
Mon May 15 04:41:41 EDT 2017

On zondag 14 mei 2017 18:22:24 CEST Ciarán wrote:
> 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);
> END$$
> 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
> possible?

I don't have much experience with mysql triggers. However it looks to me your 
trigger is a bit odd. You specify that after the new row is inserted the 
trigger should insert something again, but don't specify a full row. I don't 
think you want to insert another row in the same table. You want to alter the 
row being inserted.

Rather than a trigger AFTER INSERT, I'd use a BEFORE INSERT and in your BEGIN/
END block simply set the fields as you want. Something like this (not tested):

CREATE TRIGGER ins_transactions
BEFORE INSERT ON gnucash.transactions 
   SET NEW.post_year = year(NEW.post_date);
   SET NEW.post_month = month(NEW.post_date);
   SET NEW.post_day = day(NEW.post_date);


More information about the gnucash-devel mailing list