last_record = OLD.last_record; /* Dependancies : */ SET @last=LAST_INSERT_ID(); UPDATE categorie_produit SET [email protected] WHERE id_produit = OLD.id; UPDATE produit_attribut SET [email protected] WHERE id_produit = OLD.id; END;; DELIMITER ;; As a rule you can't have a trigger on table A trigger inserts into table A - since that could cause an endless loop.
(Trigger mutation in Oracle terms) Personally I would not do this using triggers.
The code would then do basically what you have the trigger above do. You just have to be careful, not to do what you explained ("Trigger mutation").(It might be easier to just have the code set date_v_end on the current row, and then insert a completly new row. That way you don't have to mess around with updating your referenced tables) Hi, for your information, Oracle can insert a new row in a trigger ONBEFOREUPDATE on the same table. I'm trying to create a database with history in mind (experience shows you'll have to do this one day or another).I've asked here database-design-how-to-handle-the-archive-problem but there's no better anser than the link here.
My problem is about where to do the code and technically, how (My SQL gives me headaches).First I've started doing this in Php: before doing any insert, duplicate the record mark it as "obsolete" then modify the record.But there's a dependency problem (manytomany and manytoone associations must be updated as well) which implies coding (one way or another) all the dependancies and updates that come with the tables (which is not acceptable).So I'm thinking about doing all the work on the database server side. The problem is that I have to "archive" the current record before modifying it.To do so, the code must be in a trigger "before update".Here's my code: DELIMITER ;; DROP TRIGGER IF EXISTS produit_trigger_update_before; CREATE TRIGGER produit_trigger_update_before BEFORE UPDATE ON produit FOR EACH ROW BEGIN /* */ INSERT INTO produit SET id_origine = OLD.id_origine, date_v_creation = OLD.date_v_creation, date_v_start = OLD.date_v_debut, date_v_end = NOW(), ...