Problems creating TRIGGER in MySQL

Question

I am first time using trigger concept in my MYSQL workbench like below query,

First I created people table by using below query,

CREATE TABLE people (age INT, name varchar(150));

Then I used below query for trigger

DELIMITER 
CREATE TRIGGER agecheck BEFORE INSERT ON raptor1_5.people FOR EACH ROW IF NEW.age < 0 THEN SET NEW.age = 0; END IF;

DELIMITER ;

But agecheck trigger not creating to people table. Its does not show any error message when I run this query.

Here is my table images,

enter image description here

Updated : based on the answer

enter image description here

Mad Dog Tannen · Accepted Answer · 2015-05-15 12:42:19Z

2

Try with this.

USE `raptor1_5`;

DELIMITER $$

DROP TRIGGER IF EXISTS raptor1_5.agecheck$$
USE `raptor1_5`$$
CREATE TRIGGER agecheck BEFORE INSERT ON people 
FOR EACH ROW 
BEGIN
 IF NEW.age < 0 THEN 
   SET NEW.age = 0; 
 END IF;
END$$
DELIMITER ;

answered May 15, 2015 at 12:42

Mad Dog Tannen

7,2505 gold badges34 silver badges55 bronze badges

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

4 Comments

Mad Dog Tannen Over a year ago

It tells which schema to use for the session you are in. Even though if you try to execute to create a trigger without USING a schema, it should give the error that 'no database selected'

Mad Dog Tannen Over a year ago

It was most likley not using any SCHEMA , also, try again like this now, se if it doesnt work.

Mad Dog Tannen Over a year ago

CREATE TRIGGER agecheck BEFORE INSERT ON raptor1_5.people Ive added the database name.

MMMMS Over a year ago

Nice explanation.I understand the problem. thank you

Abhik Chakraborty · Accepted Answer · 2015-05-15 12:13:18Z

0

The standard trigger syntax should be as

DELIMITER  //

CREATE TRIGGER agecheck BEFORE INSERT ON raptor1_5.people 
FOR EACH ROW 
BEGIN
 IF NEW.age < 0 THEN 
   SET NEW.age = 0; 
 END IF;
END;//

DELIMITER ;

The above is when you run on mysql cli, however in phpmyadmin or workbench check if you have an option to set the delimiter if yes choose that and remove the delimiter part from the above.

answered May 15, 2015 at 12:13

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

5 Comments

MMMMS Over a year ago

Still its not working. Its shows like Executing Query... but nothing happens

Mad Dog Tannen Over a year ago

Maybe add a delete trigger if exists before?

MMMMS Over a year ago

@KayNelson i cant get you

Mad Dog Tannen Over a year ago

Add this before the CREATE TRIGGER ....... DROP TRIGGER IF EXISTS raptor1_5.agecheck//

MMMMS Over a year ago

@KayNelson check my updated question. I tried this query but nothing happen

Collectives™ on Stack Overflow

Problems creating TRIGGER in MySQL

2 Answers 2

4 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related