CODEWITHSUNDEEP
mysqlsqltriggers
Cheryl Nielsen
Cheryl Nielsen

Reputation: 11

Creating Trigger with a concatenated string with a date in MySQL

I am trying to figure out how to create a trigger insert with a concatenated string including the date.

The trigger I am creating is created on a tbl_employee. Every time I insert a new employee into the table I want it to update another table called tbl_employee_audit with the following information.

id audit_data
1 New employee with ID = 1221 was added on Sep 21, 2016

Here is my code to create the trigger

CREATE TRIGGER adding_employee
AFTER INSERT
ON tbl_employee FOR EACH ROW
INSERT INTO tbl_employee_audit(ID, AUDIT_DATA) 
VALUES(ID, CONCAT('New employee with ID = ', new.emp_id, ‘was added on’, DATE));

The problem is that I can get it to work without the date added at the end, but if I try it with the date I get an error when inserting a new record into tbl_employees.

Error: #1054 - Unknown column 'DATE' in 'field list'

What I missing here?

THanks in advance

Upvotes: 0

Views: 683

Answers (2)

nbk
nbk

Reputation: 49395

To get your date you need to Format it

SELECT DATE_FORMAT(CURRENT_DATE, "%b %d, %Y")

| DATE_FORMAT(CURRENT_DATE, "%b %d, %Y") |
| :------------------------------------- |
| Jul 21, 2021                           |

db<>fiddle here

CREATE TRIGGER adding_employee AFTER INSERT
ON tbl_employee FOR EACH ROW
    INSERT INTO tbl_employee_audit(ID, AUDIT_DATA) 
        VALUES(ID, CONCAT('New employee with ID = ', new.emp_id, ' was added on ', DATE_FORMAT(CURRENT_DATE, "%b %d, %Y")));

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270513

Perhaps you just want the current date?

CREATE TRIGGER adding_employee AFTER INSERT
ON tbl_employee FOR EACH ROW
    INSERT INTO tbl_employee_audit(ID, AUDIT_DATA) 
        VALUES(ID, CONCAT('New employee with ID = ', new.emp_id, ' was added on ', CURRENT_DATE));

Upvotes: 1

Related Questions