use case
- use of database side programming to log data maintenance activities
- tech columns CreatedBy/CreatedOn, LastUpdatedBy/LastUpdatedOn
to store username, timestamp information on when row was
inserted / last updated defined on every application table
- no possibility for users/applications to tamper data stored in these columns
- avoid implementation of extensive protection mechanisms (e.g. by allowing
table data to be maintained only via stored procedures)
approaches investigated
1) using default column values to populate CreatedBy/CreatedOn on insert,
i.e. "CreatedBy" varchar(64) NOT NULL DEFAULT CURRENT_USER
-> column values not secured
-> works for insert statements only (can not be used for populating
columns LastUpdatedBy/LastUpdatedOn in case of updates)
2) using a generated column spec with a value expression
i.e. "CreatedBy" varchar(64) GENERATED ALWAYS AS CURRENT_USER;
-> not possible as CURRENT_USER, CURRENT_TIMESTAMP as non-determinstic
functions can not be used as value expressions
3) using INSERT triggers (with SQL statements or calling java procedures)
i.e. CREATE TRIGGER xy AFTER INSERT FOR EACH ROW UPDATE "CreatedBy"...
-> might work for insert statements, but triggering an update statement
with the insert would prevent me from defining an after update
trigger on the table which I would need to log update activity
(and which would create an infinte loop)
Can't think of a way that would work which is unfortunate as for example
PostgreSQL would allow such an implementation easily as transition values
can be referenced and updated with the system funtions associate with a
trigger(i.e. all needed is a one line statement NEW.CreatedBy = CURRENT_USER
within the trigger function).
Any suggestions? Is there really no way this can be achieved in Derby?
(other than maybe putting the tech cols on a separate table and joining the
two base tables in a view so that at least in read operations it would look
like the tech cols are held on the same table?)
Thanks
Thomas
