How to Correctly Delete Data from One Table After Deleting from Another Using MySQL Triggers?

MySQL Triggers: Deleting from One Table After Deleting from Another

Problem:

You want to create a trigger that automatically deletes rows from Table 2 (patron_info) whenever a row is deleted from Table 1 (patrons). However, you're encountering a syntax error when creating the trigger.

Solution:

The error is likely caused by an incorrect syntax in the DELETE statement within the trigger. Specifically, you need to use old.id instead of patrons.id to refer to the deleted row's ID, as per the following trigger:

<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>

Make sure to include the ; at the end of the DELETE statement, and check for proper syntax on other parts of the trigger as well.

Additionally, remember to use delimiters when entering the trigger code in a console window. For example, you can use $ as the delimiter, like so:

<code class="sql">DELIMITER $

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

DELIMITER ;

$</code>

The above is the detailed content of How to Correctly Delete Data from One Table After Deleting from Another Using MySQL Triggers?. For more information, please follow other related articles on the PHP Chinese website!