I would like to insert a record in a special table, called "changes" whenever a record in my "address" table has been modified. The record should contain the modified fields (.old <-> .new values), timestamp and the name of the logged in user, who made that modification.
So where do I have to put that code? Any suggestion and code example would be appreciated.
AFTER UPDATE question
Re: AFTER UPDATE question
I think it is too late in AFTER UPDATE because at that time old data has already been overwritten.
As a starting point:
You should consider fetching current data in BEFORE UPDATE hook, for example by using
A different approach: database triggers
As a starting point:
You should consider fetching current data in BEFORE UPDATE hook, for example by using
getRecord(tn, pk)
-function, then compare (current) data with (new) $data
array, then insert differences, timestamp and current user into CHANGES table.A different approach: database triggers
Kind regards,
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
[code]...[/code]
blocks for better readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools
Re: AFTER UPDATE question
Thanks js,
I already tried the "Trigger Approach" where it has .old and .new field values to compare. At first glance it worked well. But later I ran into one serious problem.
The address table is initially filled by different tables from my database (f.e., doctors, hospitals, etc.). These tables have a couple of fields in common (f.e. first name, street, zip-code, telephone nr, etc.). So when someone changes the value of a field in the address table, this modification has to be committed also in the table, where the address record has its origin (f.e. in doctors table). So far so good.
But when I modify f.e. the telephone number in the doctors table it has to be updated in the address table too. That again starts the trigger on the address table and a database errors occurs (REGRESSION). That seems to be a typical problem with mySQL, where you cannot temporally disable a trigger.
I already tried the "Trigger Approach" where it has .old and .new field values to compare. At first glance it worked well. But later I ran into one serious problem.
The address table is initially filled by different tables from my database (f.e., doctors, hospitals, etc.). These tables have a couple of fields in common (f.e. first name, street, zip-code, telephone nr, etc.). So when someone changes the value of a field in the address table, this modification has to be committed also in the table, where the address record has its origin (f.e. in doctors table). So far so good.
But when I modify f.e. the telephone number in the doctors table it has to be updated in the address table too. That again starts the trigger on the address table and a database errors occurs (REGRESSION). That seems to be a typical problem with mySQL, where you cannot temporally disable a trigger.
Re: AFTER UPDATE question
The "before update hook" approach will give you total control.
Kind regards,
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
[code]...[/code]
blocks for better readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools