在 MYSQL 中创建一个 BEFORE INSERT 触发器 [英] Creating a BEFORE INSERT TRIGGER IN MYSQL

问题描述

我需要帮助在 mySQL Bench 上创建一个 BEFORE INSERT TRIGGER.我是新手.

I need help creating a BEFORE INSERT TRIGGER on mySQL Bench. im new to this please.

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"以及记录的当前日期和时间被插入.

Create a BEFORE INSERT trigger on the toy table that adds a record to the toytracking table with the information from the toy table record that is being INSERTED, hard coded ToyAction that will be ‘INSERT’ and the current Date and time the record is inserted.

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:在‘新’中‘插入’未知列"

It throws me an error saying " ERROR 1054: Unknown column 'inserted' in 'NEW' "

推荐答案

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

Get rid of the IF new.inserted test, since there's no column with that name, and just hard-code INSERT as the value for the ToyAction column as you stated in the requirements.

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

这篇关于在 MYSQL 中创建一个 BEFORE INSERT 触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！