Hi,
Thomas Hill a écrit :
Do you know if it would be possible to CHANGE the new values as well?
In PL/pgSQL I would just add:
NEW.RoleName = CURENT_USER
into my after insert trigger SQL code.
How would I do that in derby?
AFAIK, with Derby, you can't *change* the value *being* inserted (in
other words, you can't modify the transitions variables/tables NEW or
NEW_TABLE). Beside, according to the reference manual
(http://db.apache.org/derby/docs/dev/ref/rrefsqlj43125.html - section
"Triggered-SQL-statement"): "Before triggers cannot have INSERT, UPDATE
or DELETE statements as their action".
However, you can *replace* a value just *after* a row was inserted by
using a trigger. Let's assume I have a Client table, and I want to log
for each row the user that inserted it. I could use:
ij> CREATE TRIGGER LogClientOwner
> AFTER INSERT ON Client
> REFERENCING NEW_TABLE AS NEW
> UPDATE Client SET Client.CreatedBy = USER
> WHERE Client.ID IN (SELECT ID FROM NEW);
0 rows inserted/updated/deleted
For this to be robust, *my users shouldn't have UPDATE permission* on
the Client table. And of course, ID must be a (primary or unique) key to
the table.
Does this cover your needs?
Bye,
Sylvain.
--
Website: http://www.chicoree.fr
