MYSQL Trigger Error,

Question

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 ;

In your title you write about an Error, what is the error? Does it have a message and/or a number? — hakre
– hakre, Commented Jan 1, 2015 at 22:58
Thanks for the reply hakre, the error had no other information than just "Error". Which is partly why I am posting. — The08jayhawk
– The08jayhawk, Commented Jan 2, 2015 at 3:47

Gordon Linoff · Accepted Answer · 2015-01-01 23:12:05Z

2

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.

edited Jan 1, 2015 at 23:12

answered Jan 1, 2015 at 22:56

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

The08jayhawk Over a year ago

Thank you Gordon, your explanation clears that up pretty well, I will try implementing it shortly. As a side note, its been awhile since I have been on here but where is the upvote button?

The08jayhawk Over a year ago

Quick update, I was able to make the first statement work however it appears that MySQL version I have doesn't allow me to have multiple triggers for the same things so I suppose I am limited in that way unless I can set it to run on UPDATE or INSERT which I don't think will work from my quick trial. Perhaps using CHANGE instead of UPDATE or INSERT would lead to the desired behavior?

The08jayhawk · Accepted Answer · 2015-01-02 17:57:33Z

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!

Collectives™ on Stack Overflow

MYSQL Trigger Error,

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related