Creating a BEFORE INSERT TRIGGER IN MYSQL

Question

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' "

It's pretty clear. You don't have column named 'inserted' on TOY table. What you want to achieve? And also, your tables design are not common. There are redundant information between those two tables. — Hermanto
– Hermanto, Commented Nov 10, 2016 at 6:42
i want to 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. — ninying90
– ninying90, Commented Nov 10, 2016 at 6:47
The question says that ToyAction should be INSERT. But your trigger sets it to either DELETE or NEW, depending on that test of a nonexistent column. — Barmar
– Barmar, Commented Nov 10, 2016 at 6:52

Barmar · Accepted Answer · 2016-11-10 06:54:52Z

1

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

answered Nov 10, 2016 at 6:54

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ninying90 Over a year ago

Thank u Barmar. this is totally different from SQL. i am new to MySQL. can you please give me an example if it was a BEFORE UPDATE trigger? Thank you!

santoshe61 Over a year ago

@Barmar ... I have also got stuck in similar situation.. do we have any option to delete column value in BEFORE INSERT TRIGGER. Like in this example can we delete new.inserted ?

Mohammad Nurul Huda Rimon · Accepted Answer · 2016-11-10 07:31:31Z

1

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 ;

edited Nov 10, 2016 at 7:31

answered Nov 10, 2016 at 7:05

Mohammad Nurul Huda Rimon

2,5351 gold badge17 silver badges18 bronze badges

4 Comments

Barmar Over a year ago

Why should he try it? What is NEW.deleted? There's no deleted column in the toy table

Mohammad Nurul Huda Rimon Over a year ago

Yes. i got it now this is my mistake.

ninying90 Over a year ago

Please any help it it was BEFORE UPDATE trigger? thank you

Mohammad Nurul Huda Rimon Over a year ago

@ninying90 use ELSEIF more about techonthenet.com/mysql/loops/if_then.php

Collectives™ on Stack Overflow

Creating a BEFORE INSERT TRIGGER IN MYSQL

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related