CODEWITHSUNDEEP
phpmysqltriggersphpmyadmin
The08jayhawk
The08jayhawk

Reputation: 23

MYSQL Trigger Error,

I am trying to create a very simple MySQL trigger that will run on the insertion or updating of a row on one table (Screws). I thought I had it pretty close however when I run the query to create the trigger, it just fails and says error, nothing to point me in the right direction.

Here is what I have so far, I am just wanting to take the value of two columns within the table and multiply them and then update the result into a third column, I want this to happen whenever a new record is added or edited within this table. I have tried matching some examples I have seen so far as best I can but nothing seems to quite match. 

delimiter //
CREATE TRIGGER estimate
AFTER INSERT ON `Screws` FOR EACH ROW
begin
UPDATE Screws SET Quantity = Weight * num_per_ounce;
end;
delimiter ;

Upvotes: 2

Views: 149

Answers (2)

The08jayhawk
The08jayhawk

Reputation: 23

Okay, I will post my eventual solution for anyone else that has a similar issue.

Turns out I forgot to change the Trigger action in the other trigger, so I have both functioning now. Here is my final code, I have added a check to prevent it from running if there is no data input in the two fields it checks. 

CREATE TRIGGER screws_insert
BEFORE INSERT ON `Screws` FOR EACH ROW
begin
IF new.Weight AND new.num_per_ounce IS NOT NULL THEN
    SET new.Quantity = new.Weight * new.num_per_ounce;
END IF;
end;

CREATE TRIGGER screws_update
BEFORE UPDATE ON `Screws` FOR EACH ROW
begin
IF new.Weight AND new.num_per_ounce IS NOT NULL THEN
    SET new.Quantity = new.Weight * new.num_per_ounce;
END IF;
end;

Be sure to set the delimiter to // before running the SQL. Thanks to both users who contributed to this solution!

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

If you want to change the same table where you are inserting a row, then don't use an after insert trigger. Instead use a before insert trigger:

delimiter //
CREATE TRIGGER screws_insert
BEFORE INSERT ON `Screws` FOR EACH ROW
begin
    SET new.Quantity = new.Weight * new.num_per_ounce;
end;
delimiter ;

You are essentially trying to add a calculated column, which MySQL does not directly support.

If you want a trigger for update as well, then you need two triggers:

delimiter //
CREATE TRIGGER screws_update
BEFORE UPDATE ON `Screws` FOR EACH ROW
begin
    SET new.Quantity = new.Weight * new.num_per_ounce;
end;
delimiter ;

Notice I changed the trigger names to include the table. This is usually a good practice to help keep track of triggers.

Upvotes: 2

Related Questions