Creating Trigger during INSERT in MYSQL showing Error

Question

Create table:

CREATE TABLE Persons (
    ID int NOT NULL AUTO_INCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Birth_Date date,
    Review_Date date,
    PRIMARY KEY (ID)
);

While inserting data into this table, I want to add a trigger that will update column Review_Date with the value 1 year from birth date. Below is the trigger I have written and it's showing an error.

CREATE TRIGGER tri_Test_Insert_Trigger 
AFTER INSERT 
ON persons
FOR Each row
BEGIN
    UPDATE persons
    SET review_date = dateadd(year,1,birth_date)
END
GO

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END GO' at line 8 0.000 sec

Edit your question and add the full text of the error message — Sloan Thrasher
– Sloan Thrasher, Commented Sep 29, 2018 at 8:11
You're missing a ; on the end of your UPDATE query and after the END — Nick
– Nick, Commented Sep 29, 2018 at 8:26
try in this code in between begin and end: UPDATE Persons SET Review_Date = DATEADD(year,1,Birth_Date); — Korat Prakash
– Korat Prakash, Commented Sep 29, 2018 at 8:36

flyingfox · Accepted Answer · 2018-09-29 09:26:06Z

3

Three things need to be updated in your trigger:

a. need to add ; at the end of your UPDATE sql
b. remove GO at the end of your trigger and use DELIMITER instead
c. dateadd needs to be changed to date_add

 DELIMITER $$
    CREATE TRIGGER tri_Test_Insert_Trigger 
    AFTER INSERT 
    ON persons
    FOR Each row
    BEGIN
        UPDATE persons
        SET review_date = date_add(year,1,birth_date); -- add ; here
    END$$
  DELIMITER ; -- remove GO and use DELIMITER instead

edited Sep 29, 2018 at 9:26

answered Sep 29, 2018 at 8:48

flyingfox

13.5k4 gold badges30 silver badges43 bronze badges

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

1 Comment

user1882624 Over a year ago

I used this code and trigger is created. But when i insert the data to the table i am getting the error "dateadd does not exist" so i tried with date_add.Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1, birth_date); END' at line 7 0.000 sec

Barbaros Özhan · Accepted Answer · 2018-09-29 15:54:29Z

For simplicity, no need to use a begin..end block or DELIMITER settings for only one statement.

By the way, you seemed to be confused between MSSQL ( where you can use dateadd() function and GO for ending blocks) and MySQL, in which use date_add() with different order and style of arguments as (birth_date, interval 1 year), using as date_add(year,1,birth_date) produces ERROR-1064.

Preventing from getting ERROR-1442, use BEFORE INSERT trigger as the below format prefixed with new. :

mysql> DROP TRIGGER tri_Test_Insert_Trigger;

mysql> CREATE TRIGGER tri_Test_Insert_Trigger
    -> AFTER INSERT
    -> ON persons
    -> FOR Each row
    ->     UPDATE persons
    ->     SET review_date = date_add(birth_date, interval 1 year);
Query OK, 0 rows affected (0.17 sec)

mysql> insert into persons(lastname,birth_date) values('Özhan',date'2018-05-15');
ERROR 1442 (HY000): Cant update table 'persons' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
mysql> DROP TRIGGER tri_Test_Insert_Trigger;
Query OK, 0 rows affected (0.17 sec)

mysql> CREATE TRIGGER tri_Test_Insert_Trigger
    -> BEFORE INSERT
    -> ON persons
    -> FOR Each row
    ->     SET new.review_date = date_add(new.birth_date, interval 1 year);
Query OK, 0 rows affected (0.13 sec)

mysql> insert into persons(lastname,birth_date) values('Özhan',date'2018-05-15');
Query OK, 1 row affected (0.09 sec)

mysql> select * from persons;
+----+----------+-----------+------------+-------------+
| ID | LastName | FirstName | Birth_Date | Review_Date |
+----+----------+-----------+------------+-------------+
|  2 | Özhan    | NULL      | 2018-05-15 | 2019-05-15  |
+----+----------+-----------+------------+-------------+
1 row in set (0.00 sec)

Collectives™ on Stack Overflow

Creating Trigger during INSERT in MYSQL showing Error

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related