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.

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?

share|improve this question
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 '12 at 11:08
    
So, the trigger actually forbids all Updates by converting them to Inserts into the same table? –  ypercube May 1 '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 1 '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 1 '12 at 11:23
    
Two questions: 1) What version of MySQL are you using? 2) What storage engine are you using? –  RolandoMySQLDBA May 1 '12 at 12:27
add comment

1 Answer 1

up vote 0 down vote accepted

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

share|improve this answer
    
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 1 '12 at 11:36
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.