MySQL Forums
Forum List  »  Triggers

Cascaded Triggers and LAST_INSERT_ID()
Posted by: Markus Ulbricht
Date: February 02, 2012 09:10PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Cascaded Triggers and LAST_INSERT_ID()
2031
February 02, 2012 09:10PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.