db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Sylvain Leroux <s...@wanadoo.fr>
Subject Re: after insert trigger calling a procedure - how can I retrieve NEW values inside the procedure?
Date Fri, 21 Aug 2009 09:51:36 GMT
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



Mime
View raw message