Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question
    
take a look at this example on SO of using new –  Jack Douglas May 6 '11 at 8:51
add comment

2 Answers

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

share|improve this answer
    
SQL 101 for Triggers. Good answer. +1 !!! –  RolandoMySQLDBA Aug 10 '11 at 14:55
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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