CODEWITHSUNDEEP
mysql
ninying90
ninying90

Reputation: 47

Creating a BEFORE INSERT TRIGGER IN MYSQL

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

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

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

Upvotes: 0

Views: 18704

Answers (2)

Mohammad Nurul Huda Rimon
Mohammad Nurul Huda Rimon

Reputation: 2525

Try this:

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 ;

Upvotes: 1

Barmar
Barmar

Reputation: 780673

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

Upvotes: 1

Related Questions