title: MYSQL- Can't update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger
body: <p>I keep receiving this error:
  "Can't update table 'wp_posts' in stored function/trigger because it is already used by statement which invoked this stored function/trigger"</p>
  
  <p>When Woocommerce inserts a record into the <code>WP_posts</code> table, I'd like the trigger to determine whether the new record being inserted has <code>post_title (column) = 'scheduled_subscription_end_of_prepaid_term' (field data)</code>. If it does, I'd like the trigger to extract an <code>ID</code> from <code>new.post_content</code> (column/data in new inserted row). </p>
  
  <p>Once the ID is extracted, I'd like to update the ID. The ID is different than the ID of the new inserted row.</p>
  
  <p>Can someone please tell me what I'm doing wrong? </p>
  
  <pre><code>delimiter //
  create TRIGGER DB.Cancel
  before INSERT ON DB.wp_posts FOR EACH ROW
  
  BEGIN
  
  DECLARE updateid bigint;
  Declare variable longtext;
  declare variable2 longtext;
  
  IF NEW.post_title = 'scheduled_subscription_end_of_prepaid_term' then
  
  set updateid := NEW.id;
  set variable := RIGHT(new.post_content, LOCATE(":", new.post_content));
  set variable2 :=  LEFT(variable, LOCATE("2052", variable)-2);
  
  update db.wp_posts
  set post_status="wc-cancelled"
  where id=variable2 and post_status='wc-processing';
  
  end if;
  END//
  </code></pre>