Knut Anders Hatlen wrote:
The update statements in the triggers don't have a WHERE clause, so each of them will update all rows in the table. Something like this should be much faster, provided that you have a user_id column with an index:create trigger ut__it after insert on usersTrigger referencing new as new for each row update usersTrigger set lc_user_name = lower(user_name) where user_id = new.user_id; It'll still not be as fast as doing the lowercasing in Java, since the update statement in the trigger will navigate through the index instead of using the cursor directly, but it should perform reasonably well, I think.
I thought row triggers were restricted to operating on just the rows that were modified (http://db.apache.org/derby/docs/10.1/ref/rrefsqlj43125.html#rrefsqlj43125__sqlj54276), I take it from what you've said that is not the case.
I found the documentation rather unclear on the difference between row and statement triggers, and also on which rows would be affected by a trigger, thanks for the clarification.
Doing the lowercasing in Java is both trivial and fast, so that seems the best option.
-- Alan Burlison --
