MySQL Trigger Not Executing like it should

Question

I wrote the trigger below, but when I insert into the table it doesn't capitalize the inserted word, like it is supposed to. (no errors either). Any possible solutions? (I have an employee and an employee2 table, since I read you cant have the trigger in the table it is trying to update).

DELIMITER $$
DROP TRIGGER IF EXISTS mytrigger$$
CREATE TRIGGER mytrigger before INSERT ON employee FOR EACH ROW
BEGIN
 update employee2
 SET employee2.FName = CONCAT(UCASE(LEFT(employee2.FName, 1)), LCASE(SUBSTRING(employee2.FName, 2)));
END;
$$
DELIMITER ;

change the event AFTER INSERT if you run UPDATE

xkeshav
– xkeshav

2012-12-05 04:30:54 +00:00
Commented Dec 5, 2012 at 4:30 — xkeshav
– xkeshav, Commented Dec 5, 2012 at 4:30

bobwienholt · Accepted Answer · 2012-12-05 04:30:57Z

3

You can have triggers on the table you are trying to update, you just can't run UPDATE queries. Instead, you modify the data before it is inserted:

DELIMITER $$
DROP TRIGGER IF EXISTS mytrigger$$
CREATE TRIGGER mytrigger before INSERT ON employee FOR EACH ROW
BEGIN
  SET NEW.FName = CONCAT(UCASE(LEFT(NEW.FName, 1)), LCASE(SUBSTRING(NEW.FName, 2)));
END;
$$
DELIMITER ;

The "NEW" keyword gives you access to the data that is going to be inserted.

answered Dec 5, 2012 at 4:30

bobwienholt

17.6k3 gold badges43 silver badges48 bronze badges

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

3 Comments

MisterNaux Over a year ago

It just seems too good to be true; too easy but THANK YOU, you have no idea how fried my brain was trying to figure this out. In all honesty, i probably would never have figure out i could use new, now I will never forget!

MisterNaux Over a year ago

Just out of curiousity, If I wanted the same trigger to also work before updates ( so to work before updates and before inserts) would I have to create a second trigger?

bobwienholt Over a year ago

Yes, you would have to create a second trigger. With update triggers you get access to both the OLD and NEW values.

Collectives™ on Stack Overflow

MySQL Trigger Not Executing like it should

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related