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

Question

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.

score 0 · Accepted Answer · answered Aug 24 '22 at 20:25

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.

Thank you so much! I've been struggling with this for the past 2 days and it was driving me crazy! I've seen the use of new.column_name before but wasn't sure what it was used for and my textbook doesn't say anything about it so far. As for the delimiter, I never thought about using one in MySQL. Also, after I inserted the statement you created it worked just fine... however, PHPMyAdmin shows the statements that were executed after doing so. I find it interesting that the statement PHPMyAdmin "executed" doesn't show the delimiter function. Weird. — Halomationdude, Aug 24 '22 at 21:21
I'm assuming you can use any symbol you want after the delimiter (within reason like not another semicolon because that would defeat the purpose). But why do I have to put a space after between the last DELIMITER and the semicolon? — Halomationdude, Aug 24 '22 at 21:24
Correct, but be careful what you use as a delimiter. You might run into collisions with reserved characters, backslash \ for example is reserved as an escape character so I would stick to the standard $$ escape characters when creating functions, procedures, and triggers [see here](https://stackoverflow.com/questions/10259504/delimiters-in-mysql). As for the space, the semicolon is not the end of the statement, its a parameter for the DELIMITER command that changes your delimiter back to semicolon [see here](https://stackoverflow.com/questions/53420927/defining-delimiters-in-mysql) — griv, Aug 24 '22 at 21:36

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

1 Answers1