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 Mon, 24 Aug 2009 15:30:14 GMT
Hi Thomas,

> (1) defining a default value on the colum (to ensure something is put there even
> if the user does not give a value on the insert itself) [...]
Beware of the DEFAULT value, since a user could send a fake value with 
the INSERT statement:

ij> -- I'm sylvain
ij> INSERT INTO Client( ... , CreatedBy) VALUES (... , 'Dilbert'); -- :(

You can only trust the content of the column CreatedBy  *after* the 
trigger that (over)write it has fired. Do not use that column before!

And (you will dislike that - if you don't think to it), if your users 
have UPDATE permission on the table, they could do something like:
ij> UPDATE Client SET CreatedBy = 'Dilbert';

Of course, this will be logged - and could be traced back to the real 
user name - but prevention is better than cure...

> (1) showing the initially inserted value on the columns and 
> (2) audit records showing the 'old' value fom the initial inserrt and records
> from after the update coming fom the second trigger - whereas I would have only
> had one entry in case I could have overwritten transition variable content in
> the first place with whatever would have been stored there prior to this being
> written back to the data base.
Concerning the "audit" part, I'm not sure to understand exactly what you 
want to log:
- only historical data (who changed what and when)
- and/or users try to do something ''illegal'' (like sending a fake 
identity in the column CreatedBy)

Neither where you want to log your data: in an other table, by writing 
to some file, ...

Anyway, remember that:
- TRIGGER AFTER INSERT allow you to access the NEW rows
- TRIGGER AFTER DELETE allow you to access the OLD rows
- TRIGGER AFTER UPDATE allow you to access both the OLD and NEW rows

> [...] whereas I would have only
> had one entry in case I could have overwritten transition variable content [...]
Finally, if you are concerned by those "two entries", due to the TRIGGER 
AFTER UPDATE, you could rewrite it to something more 'clever':
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)
 >                     AND Client.CreatedBy != USER;
                           ^^^^^^^^^^^^^^^^^^^^^^^^
                           This will no longer update the table if the
                           field CreatedBy has already the right value.


Best regards,
Sylvain

-- 
Website: http://www.chicoree.fr



Mime
View raw message