MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tbl_employee_audit (
    audit_ID MEDIUMINT NOT NULL AUTO_INCREMENT,
    audit_data varchar(100),
    PRIMARY KEY (audit_ID)
);

CREATE TABLE tbl_employee (
    emp_ID MEDIUMINT NOT NULL AUTO_INCREMENT,
    emp_name VARCHAR(100),
    PRIMARY KEY (emp_ID)
);
CREATE TRIGGER after_employee_insert AFTER INSERT
ON tbl_employee
FOR EACH ROW
BEGIN
  SET @var1 = CONCAT('New employee with ID = ', NEW.emp_ID, ' was added on ',
  CURRENT_DATE());
  INSERT INTO tbl_employee_audit
              (audit_data)
  VALUES      (@var1);
END 
INSERT INTO tbl_employee (emp_name) VALUES ('Test')
SELECT * FROM tbl_employee


emp_ID
emp_name


1
Test


SELECT * FROM tbl_employee_audit


audit_ID
audit_data


1
New employee with ID = 1 was added on 2022-08-24