Cascaded Triggers and LAST_INSERT_ID()

[Markus Ulbricht]

I have 3 tables and would like to use a unique key for all tables generated by an autoincremented id of a `MasterKeyManager` table.
For `Table_A` and `Table_B`which refers to `Table_A` I just implemented a "BEFORE INSERT" Trigger to get the key from the MasterKeyManager by the LAST_INSERT_ID function.
But this is not working If I insert a new row into `Table_B` LAST_INSERT_ID() does not return the correct id of the newly create id from `MasterKeyManager`table.

Here are my Tables:

-- MasterKeyManager
CREATE TABLE IF NOT EXISTS `autidus`.`MasterKeyManager` (
`id` INT NOT NULL AUTO_INCREMENT ,
`last_update` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
PRIMARY KEY (`id`) )
ENGINE = InnoDB;

-- Table_A
CREATE TABLE IF NOT EXISTS `autidus`.`Table_A` (
`id` INT NULL DEFAULT NULL ,
`last_update` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
PRIMARY KEY (`id`) )
ENGINE = InnoDB;

-- TRIGGER: Trigger_Table_A_before_INSERT
CREATE TRIGGER `Trigger_Table_A_before_INSERT` BEFORE INSERT ON `Table_A` FOR EACH ROW BEGIN
INSERT INTO `MasterKeyManager` (`last_update`) VALUES (NOW());
SET NEW.id = LAST_INSERT_ID();
END

-- Table_B
CREATE TABLE IF NOT EXISTS `autidus`.`Table_B` (
`id` INT NULL DEFAULT NULL ,
`last_update` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
PRIMARY KEY (`id`) )
ENGINE = InnoDB;

-- TRIGGER: Trigger_Table_B_before_INSERT
CREATE TRIGGER `Trigger_Table_B_before_INSERT` BEFORE INSERT ON `Table_B` FOR EACH ROW BEGIN
INSERT INTO `Table_A` (`last_update`) VALUES (NOW());
SET NEW.id = LAST_INSERT_ID();
END


Now I would like to insert a new row into `Table_B`
INSERT INTO `Table_B` (`last_update`) VALUES(NOW());

But what I get is this:

mysql> SELECT MasterKeyManager.id as MasterKeyID, (SELECT id from `Table_A`) AS Table_A_ID, (SELECT id from `Table_B`) AS Table_B_ID FROM MasterKeyManager;
+-------------+------------+------------+
| MasterKeyID | Table_A_ID | Table_B_ID |
+-------------+------------+------------+
| 1 | 1 | 0 |
+-------------+------------+------------+

So it seems to me that LAST_INSERT_ID() has not a global scope or will not be updated when an INSERT is done inside a Trigger.
Subsequently a second insert INTO `Table_B` delivers:

INSERT INTO `Table_B` (`last_update`) VALUES(NOW());
ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'

Any explanation + help for a solution is welcome.

Thanks,

Markus