title: MySQL trigger set values for NEW row and update another in the same table
body: <p>I have a table that I keep track of fees for a specific item. These fees can change over time so I have two columns (startDate, endDate) with the current set of fees always having an endDate in the far future. I already have a trigger that I use to do some calculations on the new row being entered but what I also want to have happen is if I enter an item that already has an entry I want to set the previous entry's endDate to the day before the new entry's startDate and the new endDate to a predetermined far-away date. Here is the code for what I tried first:</p>
  
  <pre><code>CREATE
  DEFINER=`root`@`%`
  TRIGGER `im`.`splitBeforeIns`
  BEFORE INSERT ON `im`.`split`
  FOR EACH ROW
  BEGIN
      SET NEW.tcPercent = (NEW.tcOfficeFee / NEW.globalFee) * 100 , NEW.proPercent = 100 - NEW.tcPercent, NEW.endDate = 20501231;
      UPDATE im.split set endDate = ADDDATE(NEW.startDate, -1) where procKey = NEW.procKey AND endDate = 20501231;
  END$$
  </code></pre>
  
  <p>The error I get is: </p>
  
  <pre><code>ERROR 1442: Can't update table 'split' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
  </code></pre>