How to Fix a Syntax Error in MySQL Trigger: Deleting from \"patron_info\" After Deleting from \"patrons\"?

MySQL Trigger: Delete from "patron_info" After Deleting from "patrons"

To establish a trigger that automatically deletes rows from the "patron_info" table when corresponding rows are removed from the "patrons" table:

Syntax Error Correction:

The original trigger syntax error stems from attempting to use both "patrons.id" and "old.id" in the "WHERE" clause. To correctly delete rows from "patron_info" based on the deleted "patron" ID, the trigger should use "old.id":

<code class="sql">CREATE TRIGGER log_patron_delete AFTER DELETE on patrons
FOR EACH ROW
BEGIN
  DELETE FROM patron_info
    WHERE patron_info.pid = old.id;
END</code>

Additional Considerations:

• Ensure a semicolon (";") terminates the DELETE statement within the trigger.
• Use delimiters when entering the trigger code through the console (e.g., "DELIMITER //").

The above is the detailed content of How to Fix a Syntax Error in MySQL Trigger: Deleting from \"patron_info\" After Deleting from \"patrons\"?. For more information, please follow other related articles on the PHP Chinese website!