Here, there's a sample of a trigger with "INSTEAD OF UPDATE" clause:

CREATE TRIGGER [dbo].[Test_PTA_Table_Update_trg]
-- ALTER TRIGGER [dbo].[Test_PTA_Table_Update_trg]
   ON  [dbo].[Test_PTA_Table]
   DECLARE @key int
   SET @key = (SELECT TestTablePK FROM Inserted)

   UPDATE Test_PTA_Table
      SET DateEnd = GetDate(), DateReplaced = GetDate()
      WHERE TestTablePK = @key

   INSERT INTO dbo.Test_PTA_Table
   (TestTableText, DateCreated, DateEffective, OperatorCode, DateReplaced)
   (SELECT TestTableText, GetDate(), GetDate(), OperatorCode, NULL
FROM Inserted)

I'm trying to port the principle to MySQL:


DROP TRIGGER IF EXISTS attribut_trigger_update_before;

CREATE TRIGGER attribut_trigger_update_before
    INSERT INTO attribut SET 
      id_origine      = OLD.id_origine,
      date_v_creation = OLD.date_v_creation,
      date_v_debut    = OLD.date_v_debut,
      date_v_fin      = OLD(),
      importance      = OLD.importance,
      description     = OLD.description,
      valeur          = OLD.valeur,
      abbreviation    = OLD.abbreviation;


But it doesn't work.

mysql> update produit set importance=3;
ERROR 1442 (HY000): Can't update table 'produit' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Any idea how to do this?

  • 1
    MySQL does not support INSTEAD OF UPDATE triggers. What is the requirement? To keep an audit trail? Inserting into the same table whenever a row in it is updated seems like a strange requirement. Normally you'd audit to a secondary table.
    – Philᵀᴹ
    May 1, 2012 at 11:08
  • So, the trigger actually forbids all Updates by converting them to Inserts into the same table? May 1, 2012 at 11:10
  • You're not allowed to do an insert/update/delete when you're in a trigger of the same table, which is a clear sign that MySQL has still a loooong way to go to catch up with system that do this (SQL server and PostGRESQL). And that is a real problem, I'm looking for a workaround. May 1, 2012 at 11:21
  • @Phil It's not to keep an audit trail, it's to remember easily what has been done and to be able to easily make request using something like "date of validity" of a product or anything in my database (= generic behavior). May 1, 2012 at 11:23
  • Two questions: 1) What version of MySQL are you using? 2) What storage engine are you using? May 1, 2012 at 12:27

1 Answer 1


This has been discussed before by others

Your question is a little different in this instance. You may have to experiment with INSERT IGNORE or REPLACE for the desired effect.

You may need to also experiment with disabling the trigger midstream : Disable trigger for just one table

  • Thanks for your answer, but the first link is not what I'm looking for (= I don't want to make a column in a relation unmodifyable for consistency reasons), and the second link is about making a separate table for audit trail, which is not what I want. I want to be able to fetch instantaneously a product with a specific date of validity (using date_v_start and date_v_end fields in my sample). So I can't archive, or only log actions: I must have both copies of the same product, before and after the modification. May 1, 2012 at 11:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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