MYSQL BEFORE DELETE Trigger history table update with revision

I've got a simple component database with some tables that contain from dozens up to hundred or so part rows with bunch of columns as parameters. I've implemented a simple history table logging to record changes over time, mostly as a recovery in case of butterfingers.

I'm basically doing what's suggested here: Is there a MySQL option/feature to track history of changes to records?

This is how the history table is created:

CREATE TABLE Altium.Discrete_History LIKE Altium.Discrete; 
ALTER TABLE Altium.Discrete_History 
    DROP PRIMARY KEY, 
    ENGINE = MyISAM,    
    DROP INDEX `PartName_UNIQUE`,   
    DROP INDEX `ComponentId_UNIQUE`,    
    ADD `Action` VARCHAR(8) DEFAULT "Insert" FIRST,     
    ADD `Update` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `Action`,    
    MODIFY Revision INT(6) AFTER `ComponentId`,     
    ADD PRIMARY KEY (ComponentId, Revision);

Modifying revision does nothing basically, I wanted it to be after the action-column I couldn't figure out a way to skip a column in the select loop, without going to a lot of trouble about it.

The difference is that my main table has a revision number, which is nice to have if the database is carved out as an access database and passed to a contractor/client/whatever. There's no problem updating the revision number on INSERT or UPDATE, however on DELETE this crashes.

You can't update the OLD value on BEFORE DELETE and there's no NEW value to update. UPDATE ON DUPLICATE KEY sounds like it should work, but it just silently fails, or rather updates the existing entry in the history table.

So this is what I'm trying to do:

CREATE TRIGGER Altium.Discrete_bd BEFORE DELETE ON `Discrete` 
FOR EACH ROW 
INSERT INTO Altium.Discrete_History 
(SELECT 'Delete', NOW(), d.* 
FROM Altium.Discrete AS d 
WHERE d.ComponentId = OLD.ComponentId)
ON DUPLICATE KEY 
UPDATE Revision = VALUES(Revision) + 1

Before deleting that "tester" part, I have this in history:

And after I delete the part from Discrete-table, I get this:

So.. Indubitably it is incrementing the conflicting cell value, but I'm not getting a new "delete" line with updated revision, instead the last legitimate entry is incremented.

How to get what I was after i.e. a line with the delete action that has last data for that part?

EDIT This is what happens with update trigger, change gets logged in the history as expected.

Stretching my minuscule MySQL skills to the limit, I came up with this. It does what I wanted. So I'm creating a scratch table to copy the deleted line into, which causes no conflicts, update the offending revision number and insert the temporary data into the history table. Parsing this into a select statement to generate the relevant queries for all component tables will take a bit, though..

    CREATE TRIGGER `Discrete_before_delete` BEFORE DELETE ON `Discrete` FOR EACH ROW BEGIN
DROP TEMPORARY TABLE IF EXISTS TempTable_Discrete;
CREATE TEMPORARY TABLE TempTable_Discrete SELECT * FROM Altium.Discrete_History LIMIT 0;
INSERT INTO TempTable_Discrete SELECT 'Delete', NOW(), d.* FROM Altium.Discrete AS d WHERE d.ComponentId = OLD.ComponentId;
UPDATE TempTable_Discrete SET Revision = Revision+1;
INSERT INTO Altium.Discrete_History SELECT cell.* FROM TempTable_Discrete AS cell;
DROP TEMPORARY TABLE IF EXISTS TempTable_Discrete;
END

Now I get a delete-line in the history as expected.

In case anyone's interested, here's a query to craft before-delete triggers for all not-history tables. The resultant table of queries needs delimiter @@ first and delimited ; last.

SELECT CONCAT("DROP TRIGGER IF EXISTS Altium.", +t.TABLE_NAME, "_bd;",
    "CREATE TRIGGER Altium.", +t.TABLE_NAME, "_bd BEFORE DELETE ON `", 
    +t.TABLE_NAME, "` FOR EACH ROW BEGIN",
    " DROP TEMPORARY TABLE IF EXISTS Temp_", +t.TABLE_NAME, ";",
    " CREATE TEMPORARY TABLE Temp_", +t.TABLE_NAME, " SELECT * FROM Altium.", +t.TABLE_NAME, "_History LIMIT 0;",
    " INSERT INTO Temp_", 
    +t.TABLE_NAME, " SELECT 'Delete', NOW(), d.* FROM Altium.", +t.TABLE_NAME, 
    " AS d WHERE d.ComponentId = OLD.ComponentId;",
    " UPDATE Temp_", +t.TABLE_NAME, " SET Revision = Revision+1;",
    " INSERT INTO Altium.", +t.TABLE_NAME, "_History SELECT cell.* FROM Temp_", +t.TABLE_NAME, " AS cell;",
    " DROP TEMPORARY TABLE IF EXISTS Temp_", +t.TABLE_NAME, "; END@@"
) as BunchOfTriggers
FROM information_schema.TABLES t
WHERE TABLE_SCHEMA = "Altium" 
AND NOT TABLE_NAME LIKE '%History'