在MYSQL中创建BEFORE INSERT触发器

Question

我需要帮助在mySQL Bench上创建BEFORE INSERT触发器。即时通讯新的这个请。

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`)); 

创建上，增加了一个记录到toytracking表从玩具表记录正被插入的信息，硬编码ToyAction，这将是“插入”和当前日期的玩具表中的BEFORE 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：在‘插入’未知列‘新’”

Answer 1

摆脱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 

Answer 2

试试这个：

DELIMITER $$ 

CREATE 
    TRIGGER toy_before_insert BEFORE INSERT 
    ON toy 
    FOR EACH ROW BEGIN 
     IF NEW.Toyaction THEN 
      SET @Toyaction = 'DELETE'; 
     ELSE 
      SET @Toyaction = 'NEW'; 
     END IF; 

     INSERT INTO toytracking (toyId, ToyName, ToyCost, Toyaction, ActionDate) VALUES (NEW.Toyid, NEW.ToyName, NEW.ToyCost, @Toyaction, NOW()); 

END$$ 

DELIMITER ;