CODEWITHSUNDEEP
mysql
saba kavtaradze
saba kavtaradze

Reputation: 13

How to add mysql Trigger?

I need trigger to add current date (only date not time) on every insert in my table. 

CREATE TRIGGER set_created_date
BEFORE INSERT ON people
FOR EACH ROW BEGIN
SET NEW.created_date = date();
END;

this is my code. what am i doing in wrong? thank you

Upvotes: 1

Views: 67

Answers (2)

Sebastian Brosch
Sebastian Brosch

Reputation: 43574

You can use the following solution:

USE db_name; -- or select the database with script / tool

DELIMITER |

CREATE TRIGGER set_created_date BEFORE INSERT ON people 
FOR EACH ROW 
BEGIN 
    SET NEW.created_date = CURDATE(); 
END;
|

DELIMITER ;

notes / explanation:

  • You need to use DELIMITER so MySQL can handle the ; correctly.
  • The DATE function extracts the date part of a datetime value.
  • You want to set the current date so you can use CURDATE or DATE(NOW()).

demo: https://www.db-fiddle.com/f/e96B65hDocUBpNgobXaHiK/0

Difference between this TRIGGER and a column DEFAULT:

In case you are using a DEFAULT value on the column created_date the default value is only set to the column if no value is provided for this column on INSERT. So if someone run a INSERT command like the following:

INSERT INTO people (name, created_date) VALUES ('John Doe', '2017-02-03');

the default value (the current date) is not set to the column. Instead the available value will be set to the column (2017-02-03).

The TRIGGER set the current date on every INSERT. If someone execute the above INSERT command still the current date will be set to the created_date column (not the value provided on the INSERT command).

Upvotes: 3

Jorge Campos
Jorge Campos

Reputation: 23361

Why a trigger? Why not set your create_date field default now()

 alter table people modify column create_date datetime default now();

Here it is showing how it works: http://sqlfiddle.com/#!9/50a441/1

Upvotes: 1

Related Questions