MySQL: Try to Port a Trigger from SQL Server

Question

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]
   INSTEAD OF UPDATE
AS
   SET NOCOUNT ON
   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:

DELIMITER ;;

DROP TRIGGER IF EXISTS attribut_trigger_update_before;

CREATE TRIGGER attribut_trigger_update_before
BEFORE UPDATE ON attribut
  FOR EACH ROW BEGIN
    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;
  END;;

DELIMITER ;;

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

Any idea how to do this?

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 01 '12 at 11:08
So, the trigger actually forbids all Updates by converting them to Inserts into the same table? — ypercubeᵀᴹ, May 01 '12 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. — Olivier Pons, May 01 '12 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). — Olivier Pons, May 01 '12 at 11:23
Two questions: 1) What version of MySQL are you using? 2) What storage engine are you using? — RolandoMySQLDBA, May 01 '12 at 12:27

score 0 · Accepted Answer · edited Apr 13 '17 at 12:42

0

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

edited Apr 13 '17 at 12:42

Community

1

answered May 01 '12 at 11:20

RolandoMySQLDBA

182,700
33
317
520

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. – Olivier Pons May 01 '12 at 11:36

MySQL: Try to Port a Trigger from SQL Server

1 Answers1