Series of statements/variables works when run in MySQL but not in PHPMyAdmin-CodePudding

I'm trying to create a couple of triggers for a homework assignment that seems simple enough, but I've run into one problem after another. Myself and my classmates have been tasked with creating an Online Food Ordering Database in PHPMyAdmin. It's pretty basic stuff as an introductory course and I did take an SQL course a couple of years ago at a previous institution, so it's all coming back to me.

Despite my previous experience, I've never dealt with triggers before nor have I used PHPMyAdmin but I'm getting used to it. My database has 8 tables but the only two that matter for this situation are tbl_employee and tbl_employee_audit. Our assignment had us create tbl_employee_audit with an auto-incrementing "ID" which is the primary key and a varchar(100) for footnotes. We are supposed to create two triggers: one that indicates the newest addition to the employee table and one that indicates when an employee has been deleted from the table. We record these changes in the tbl_employee_audit table in the format of "New employee with ID = (insert ID) was added on (insert date)". Seems simple enough...

As I stated in the title of this post, I've developed a piece of code that works when I run it in the SQL tab of PHPMyAdmin, but when I try to put it into a trigger, I get an error message saying the syntax for line 2 (which is @varID) is incorrect. Here is what I have:

SET @varDate = CURRENT_DATE();
SET @varID = (SELECT emp_ID FROM tbl_employee WHERE emp_ID = (SELECT MAX(emp_ID) FROM tbl_employee));

SET @string1 = 'New employee with ID = ';
SET @string2 = ' was added on ';

SET @var1 = CONCAT(@string1, @varID, @string2, @varDate);

INSERT INTO tbl_employee_audit(audit_data) VALUES (@var1);

I tried creating my own trigger in the SQL tab but didn't get that to work so I've been trying to use the "Create New Trigger" function found in the Triggers tab. I called the trigger "after_employee_insert", set the Table to "tbl_employee", set the Time to "AFTER", set the Event to "INSERT", and tried to get my code to work in the definition box.

CodePudding user response：

First, you do not need to get the most recent emp_ID using a query within the trigger. You can get it directly during the insert using NEW.emp_ID.

"Within the trigger body, the OLD and NEW keywords enable you to access columns in the rows affected by a trigger. OLD and NEW are MySQL extensions to triggers; they are not case-sensitive."

See here

Second, be sure to change the delimiter using DELIMITER $$ when you're creating a new trigger. MySQL sees the first ; semicolon as the end of the CREATE TRIGGER statement.

Lastly, be sure to end the create trigger statement with END$$ not END; since we changed the delimiter to $$ on the first line. Follow that up with DELIMITER ; to change the delimiter back to a semicolon ;, also be sure to include a space between DELIMITER and ;

DELIMITER $$
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$$
DELIMITER ;

Fiddle

Note regarding the Fiddle syntax above: DELIMITER isn’t supported by the PHP driver, because it is not a mysql database command, but mysql client command.

Write a similar trigger for your delete trigger, changing the trigger to AFTER DELETE and OLD.emp_ID.