Saving with new mysql 5.7 triggers in db

[Geert Janssens]

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
> 
> 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?

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):

DELIMITER $$
CREATE TRIGGER ins_transactions
BEFORE INSERT ON gnucash.transactions 
FOR EACH ROW
BEGIN
   SET NEW.post_year = year(NEW.post_date);
   SET NEW.post_month = month(NEW.post_date);
   SET NEW.post_day = day(NEW.post_date);
END$$
DELIMITER ;

Geert