|
There are two tables and a trigger that generates the value of one of the columns of the second table before inserting a new row:
CREATE TABLE `table1` (
|
`Id` char(36) NOT NULL,
|
PRIMARY KEY (`Id`),
|
UNIQUE KEY `Id_UN` (`Id`)
|
);
|
|
CREATE TABLE `table2` (
|
`Id` int(11) NOT NULL AUTO_INCREMENT,
|
`Index` int(11) NOT NULL,
|
`Value` varchar(1024) NOT NULL,
|
`FirstTableId` char(36) NOT NULL,
|
PRIMARY KEY (`Id`),
|
UNIQUE KEY `Id_UN` (`Id`),
|
UNIQUE KEY `Value_UN` (`Value`,`FirstTableId`) USING HASH,
|
KEY `IX_FK_Table1Table2` (`FirstTableId`),
|
CONSTRAINT `FK_Table1Table2` FOREIGN KEY (`FirstTableId`) REFERENCES `table1` (`Id`) ON DELETE CASCADE ON UPDATE NO ACTION
|
);
|
|
DELIMITER ;;
|
|
CREATE TRIGGER `OnTable2BeforeInsert`
|
BEFORE INSERT
|
ON table2 FOR EACH ROW
|
BEGIN
|
IF(SELECT COUNT(*) FROM `table2` WHERE `FirstTableId` = NEW.`FirstTableId`) = 0 THEN
|
SET NEW.`Index` = 1;
|
ELSE
|
SET NEW.`Index` = (SELECT MAX(`Index`) FROM `table2` WHERE `FirstTableId` = NEW.`FirstTableId`)+1;
|
END IF;
|
END
|
|
;;
|
|
An error occurs when inserting a row into the second table:
|
INSERT INTO table1 (Id) VALUES ('7a4b8cc2-913b-43a9-ac3c-d9afb46fde98');
|
INSERT INTO table2 (Value, FirstTableId)VALUES ('123', '7a4b8cc2-913b-43a9-ac3c-d9afb46fde98');
|
|
Error: SQL Error [1364] [HY000]: (conn=37) Field 'DB_ROW_HASH_1' doesn't have a default value
|