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: automatically (re)computing last modified time?
Date Mon, 06 Jul 2009 16:11:24 GMT
Hi Peri,

Yes, I think that triggers would be your best solution for this problem.

You could use a generation clause to stuff the timestamp field via a 
function which returns the current time given all of the other columns 
as arguments. This would be an awkward solution for the following reasons:

1) Dishonesty. When you registered this function with Derby, you would 
have to lie and say that it is deterministic.

2) Brittleness. If you added another column to the table, then the 
function would not be fired when you updated the new column. That is 
because the generation clause didn't mention the new column. To get 
around this problem, every time you added a new column to the table, you 
would have to change the signature of your function and drop and 
recreate the generated column.

Hope this helps,
-Rick

Peri Tarr wrote:
> Hello,
>
> For each update to a row in a particular table, I would like to store 
> the last user who modified it and the timestamp when it was modified. 
> I had thought to use Derby's nifty GENERATED ALWAYS for these:
>
>      alter table people add lastModified GENERATED ALWAYS AS 
> (CURRENT_TIMESTAMP);
>      alter table people add lastModifiedBy GENERATED ALWAYS AS 
> (CURRENT_USER);
>
> Unfortunately, neither CURRENT_TIMESTAMP nor CURRENT_USER are 
> permitted for automatically generated columns (the documentation says 
> so; I just hadn't noticed).
>
> Is the intent for people to do this with triggers or some other mechanism?
>
> Thanks very much in advance!
>      Peri


Mime
View raw message