CODEWITHSUNDEEP
mysqlsqltriggersdatabase-trigger
user1882624
user1882624

Reputation: 333

Creating Trigger during INSERT in MYSQL showing Error

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

Upvotes: 0

Views: 1297

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65408

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)

Upvotes: 2

flyingfox
flyingfox

Reputation: 13506

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

Upvotes: 3

Related Questions