db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: common use case for data base programming not possible in derby?
Date Mon, 15 Feb 2010 16:12:42 GMT
Hi Thomas,

I am attaching a trigger-based solution which I think gives you airtight 
protection for

inserts and updates of createdBy
inserts and updates of updatedBy
updates of createdOn

The solution is not airtight, but might be good enough for

inserts of createdOn
inserts and updates of updatedOn

The solution consists of two vetting and logging procedures (found in 
zz.java). A sample script for declaring and testing the solution can be 
found in zz.sql.

Hope this helps,

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

View raw message