mysql - 在MySQL中创建插入触发器之前

我需要在mySQL Bench上创建INSERT TRIGGER之前的帮助。我是新来的。

CREATE TABLE `quincyninying`.`toytracking` (
`Toyid` INT NOT NULL,
`ToyName` VARCHAR(50) NULL,
`Toycost` DECIMAL NULL,
`ToyAction` VARCHAR(50) NULL,
`ActionDate` DATETIME NULL,
 PRIMARY KEY (`Toyid`));

CREATE TABLE `quincyninying`.`toy` (
`Toyid` INT NOT NULL,
`ToyName` VARCHAR(50) NULL,
`Toycost` DECIMAL NULL,
PRIMARY KEY (`Toyid`));

在玩具表上创建一个BEFORE INSERT触发器，将一条记录添加到玩具跟踪表中，其中包含正在插入的玩具表记录中的信息，硬编码的ToyAction（将为“ INSERT”）以及插入记录的当前日期和时间。

ERROR 1054: Unknown column 'inserted' in 'NEW' SQL Statement:
CREATE DEFINER = CURRENT_USER TRIGGER `quincyninying`.`toy_BEFORE_INSERT` BEFORE INSERT ON `toy`
FOR EACH ROW
BEGIN
    IF new.inserted THEN
        SET @toyaction = 'DELETE';
    ELSE
        SET @toyaction = 'NEW';
    END IF;

    INSERT INTO `quincyninying`.`toytracking` (toyId, ToyName, ToyCost,     Toyaction, ActionDate)
    VALUES       (new.toyid, new.Toyname, new.Toycost,@Toyaction, now());

END

它抛出一个错误，提示我“错误1054：'NEW'中插入了未知列”

最佳答案

摆脱IF new.inserted测试，因为没有该名称的列，而只是硬编码INSERT作为ToyAction列的值，如您在需求中所述。

CREATE DEFINER = CURRENT_USER TRIGGER `quincyninying`.`toy_BEFORE_INSERT` BEFORE INSERT ON `toy`
FOR EACH ROW
BEGIN
    INSERT INTO `quincyninying`.`toytracking` (toyId, ToyName, ToyCost, Toyaction, ActionDate)
    VALUES (new.toyid, new.Toyname, new.Toycost, 'INSERT', now());
END