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
