Why Am I Getting a \'Check Syntax Near END on Line 6\' Error When Creating a MySQL Trigger to Delete From a Table After a DELETE?

MySQL Trigger: Delete From Table AFTER DELETE - Syntax Error and Resolution

When attempting to create a trigger to delete rows from the 'patron_info' table after a row is deleted from the 'patrons' table, an error message of "Check syntax near END on line 6" may be encountered. This error indicates a syntax error in the trigger code.

The provided trigger code currently has a syntax error in the WHERE clause of the DELETE statement:

DELETE FROM patron_info
    WHERE patron_info.pid = patrons.id

The issue here is that the trigger references the 'patrons' table using the 'patrons' alias, which is only available within the BEGIN and END blocks of the trigger. To resolve this, the DELETE statement should instead reference the 'old' alias, which represents the deleted row from the 'patrons' table:

DELETE FROM patron_info
    WHERE patron_info.pid = old.id

With this correction, the trigger code should now be:

CREATE TRIGGER log_patron_delete AFTER DELETE on patrons
FOR EACH ROW
BEGIN
DELETE FROM patron_info
    WHERE patron_info.pid = old.id;
END

Additionally, it's important to ensure that the DELETE statement ends with a semicolon (;) to terminate the statement. When entering the trigger code in the console, delimiters should also be used to define the trigger boundaries.

The above is the detailed content of Why Am I Getting a \'Check Syntax Near END on Line 6\' Error When Creating a MySQL Trigger to Delete From a Table After a DELETE?. For more information, please follow other related articles on the PHP Chinese website!