Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: domain of gcaddu-derby-user@m.gmane.org
 designates 80.91.229.12 as permitted sender)
To: derby-user@db.apache.org
From: Thomas <Thomas.K.Hill@t-online.de>
Subject: common use case for data base programming not possible in derby?
Date: Sun, 14 Feb 2010 14:03:59 +0000 (UTC)
Lines: 40
Message-ID: <loom.20100214T150321-948@post.gmane.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
User-Agent: Loom/3.14 (http://gmane.org/)
Sender: news <news@ger.gmane.org>

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