I want to build a solution with Andomeda, but I need some form of audit/historie tables.
I saw the topic mentioned in the documentation. It should be possible with the cascading options.
Is there a sample definition available how to do this?
Thanks in advance.
Last response from Kenneth:
I wonder if we can do it less verbosely. I'm thinking of something like this:
description: Schema of database
# here is the key trick. This single setting would
# -> Create a table a_schemas_history
# -> Put a column ts_audit (or somesuch name) into the child table
# -> Create triggers to push the insert and updates there
# This setting would cause the child table to automatically contain
# all columns in the parent table
# these columns would automatically be audited
# Let's turn off auditing for a particular column
As for security, the audit table would get the same SELECT permission as its parent table. It would be denied UPDATE, DELETE and INSERT to all users.
We would still need to define the purge process, what group or user is authorized to delete rows. One way to do this is to build stored procedures that do them and assign EXECUTE privileges to one particular group, perhaps the "purge" group, and have them go by date. A simpler procedure would simply be to allow DELETEs to some audit-purge group. Then we'd need a UI utility to launch that process. I have not really thought that through yet.