Saving with new mysql 5.7 triggers in db

[Ciarán]

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

DELIMITER $$
CREATE TRIGGER ins_transactions
AFTER INSERT ON gnucash.transactions 
FOR EACH ROW
BEGIN
 INSERT INTO gnucash.transactions
 SET ACTION = 'UPDATE',
	post_year = year(post_date),
    post_month = month(post_date),
    post_day = day(post_date);
END$$
DELIMITER ;

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?






--
View this message in context: http://gnucash.1415818.n4.nabble.com/Saving-with-new-mysql-5-7-triggers-in-db-tp4691623.html
Sent from the GnuCash - Dev mailing list archive at Nabble.com.