DB2 Trigger: only update current row

Question

I've created following trigger :

CREATE TRIGGER probeer2
  AFTER INSERT ON libtn01leb.kofax_release
  FOR EACH ROW
  UPDATE libtn01leb.kofax_release SET RRNR='bleh'

This trigger will update EVERY ROW in the table, instead of the row I'm currently adding. How can I make it only be valid for the row I'm inserting ?

Answer 1

Your UPDATE statement is wrong. It is updating all the records in the table because it doesn't have any WHERE clause. If you want to update only the current row, you need to use the updated/inserted records (like in Jack's example: REFERENCING NEW AS N_ROW and use it as filter).

Answer 2

Pulling the information from the others, here's how the trigger should look

CREATE TRIGGER probeer2
  AFTER INSERT ON libtn01leb.kofax_release
  REFERENCING NEW AS N_ROW
  FOR EACH ROW
    UPDATE libtn01leb.kofax_release 
      SET RRNR='bleh'
      WHERE <unique identifier> = N_ROW.<unique identifier>

Of course, the unique identifier needs added to the above where clause.