Saving with new mysql 5.7 triggers in db

[Geert Janssens]

On maandag 15 mei 2017 17:24:45 CEST Derek Atkins wrote:
> Geert Janssens <geert.gnucash at kobaltwit.be> writes:
> > On zondag 14 mei 2017 18:22:24 CEST Ciarán wrote:
> >> 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.
> 
> Is it possible that GnuCash just isn't being happy with the "modified"
> tables?
> 
Possibly and that's what John also suggests. I don't know the exact details of 
how the sql backend interacts with the tables. I just know there are ways in 
general that you can have extra columns in an sql table than there are columns 
used in an insert/update query. When columns are omitted they are normally set 
to their default value on insert or ignored on an update. I used that idea to 
suggest my alternative trigger.

On the other hand if the sql backend for some reason sets restrictions on the 
available columns this may be an issue. Only a real test can tell but I'm not 
interested enough right now to spend the effort. On the other hand if someone 
else is, I would appreciate to hear the result.

And John's other remark was even more to the point. Why not use the year(), 
month() and day() function directly in the queries instead of duplicating the 
info in the table ?

Geert