SO-43416924-IGNORE-v1, MySQL

Language: Layout:

#MySQL 5.7.12
#please drop objects you've created at the end of the script 
#or check for their existance before creating
#'\\' is a delimiter

DROP TABLE IF EXISTS `warehouse`, `inventory`;

CREATE TABLE `warehouse` (
    `bin` VARCHAR(6) NOT NULL,
    `qty` INT DEFAULT 1,
    PRIMARY KEY(`bin`)
);

CREATE TABLE `inventory` (
    `item` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(10),
    PRIMARY KEY(`item`),
    UNIQUE KEY(`name`)
);

CREATE TRIGGER `trg_bi_inventory` BEFORE INSERT ON `inventory`
FOR EACH ROW
BEGIN
    DECLARE `_item` INT DEFAULT (SELECT `item`
                                 FROM `inventory`
                                 WHERE `name` = NEW.`name`);
    IF `_item` IS NOT NULL THEN
        UPDATE `warehouse`
        SET `qty` = `qty` + 1
        WHERE `bin` = `_item`;
    END IF;
END\\

CREATE TRIGGER `trg_ai_inventory` AFTER INSERT ON `inventory`
FOR EACH ROW
    INSERT INTO `warehouse` (`bin`)
    VALUES (NEW.`item`);

INSERT IGNORE `inventory` (`name`)
VALUES ('hammer');

SELECT `item`, `name`
FROM `inventory`;

SELECT `bin`, `qty`
FROM `warehouse`;

INSERT IGNORE `inventory` (`name`)
VALUES ('hammer');

SELECT `item`, `name`
FROM `inventory`;

SELECT `bin`, `qty`
FROM `warehouse`;

INSERT IGNORE `inventory` (`name`)
VALUES ('pliers');

SELECT `item`, `name`
FROM `inventory`;

SELECT `bin`, `qty`
FROM `warehouse`;

DROP TABLE IF EXISTS `warehouse`, `inventory`;


absolute service time: 0,37 sec