The 365 Data Science team is proud to invite you to our own community forum. A very well built system to support your queries, questions and give the chance to show your knowledge and help others in their path of becoming Data Science specialists.
Ask
Anybody can ask a question
Answer
Anybody can answer
Vote
The best answers are voted up and moderated by our team

Trigger before insert

Trigger before insert

Super Learner
0
Votes
5
Answer

Hi there,
Tried to use date_format(sysdate(), ‘%y-%m-%d’) but it got me an error message. When I used sysdate() then it’s fine in the trigger exercise. Why is this? Anything wrong or I need to change setting for my Workbench?
DELIMITER $$
CREATE TRIGGER check_hire_date
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN

IF NEW.hire_date > SYSDATE() THEN
SET NEW.hire_date = sysdate();
END IF;
END$$
DELIMITER ;
#####
official solution is as follows:
CREATE TRIGGER trig_hire_date
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF NEW.from_date > date_format(sysdate(), ‘%Y-%m-%d’) THEN
SET NEW.from_date = date_format(sysdate(), ‘%Y-%m-%d’);
END IF;
END $$
DELIMITER ;
 

5 Answers

365 Team
0
Votes

Hi Yunfeng!
Thanks for reaching out.
There’s no need to change the settings at this stage at least.
Can you please support your question with the entire error message you’ve obtained (either the text or as a screenshot)? 
In this way, we will be able to assist you better. Thank you.
Looking forward to your reply.
Best,
Martin

Super Learner
0
Votes

This is the error message:
Error Code: 1292. Truncated incorrect date value: ‘2020-May-18th’
 
The code I used was %Y-%M-%D… I figure I cannot do capital letters for m and d, but for Y it’s ok?
IF NEW.hire_date > date_format(SYSDATE(), ‘%Y-%M-%D’) THEN
SET NEW.hire_date = date_format(sysdate(), ‘%Y-%M-%D’);
END IF;
 
I inserted this below:
INSERT INTO employees
VALUES (‘999996’, ‘1990-03-01’, ‘JOHN’, ‘SMITH’, ‘M’, ‘2021-01-01’);

365 Team
0
Votes

Hi Yunfeng!
Thank you very much for your reply.
Yes, whether you use capital or small letter matters. You need to use small letters in this example.

%y-%m-%d

Regarding the error message – have you executed the queries you’ve provided in the given order? The error message is actually suggesting that you had probably used the INSERT statement to insert a different record:

INSERT INTO employees
VALUES (‘999996’, ‘1990-03-01’, ‘JOHN’, ‘SMITH’, ‘M’, ‘2020-May-8th’);

Instead, we must use numbers to express the dates – 2020-05-08.
Hope this helps.
Best,
Martin

Super Learner
0
Votes

I didn’t insert a record with “2020-May_8th” for sure. And I am quite confused when I saw this date format myself.

365 Team
0
Votes

Hi Yunfeng!
Thank you very much for your reply.
I am sorry the previous suggestion did not help. In that case, can you please post the entire code you’ve executed, as well as a screenshot containing the error message that you get (or you can also paste the error message, if you prefer)? I would like to execute the query on my end to see what we can do.
Thank you!
Looking forward to your reply.
Best,
Martin

×
LAST CHANCE
Ready to Learn Data Science?
50% OFF