2

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 ?

  • take a look at this example on SO of using new – Jack Douglas May 6 '11 at 8:51
5

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).

  • SQL 101 for Triggers. Good answer. +1 !!! – RolandoMySQLDBA Aug 10 '11 at 14:55
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.