MySQL Trigger: Deleting Data from a Related Table Upon Deletion

When managing database tables, it is common to encounter scenarios where changes in one table should cascade to other related tables. In this specific instance, the task at hand is to create a MySQL trigger that ensures that upon deleting a record from the "patrons" table, the corresponding information associated with that patron in the "patron_info" table is also removed.

Trigger Implementation

To achieve this, the following trigger can be defined:

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

Here's a breakdown of the trigger:

• log_patron_delete: The name of the trigger.
• AFTER DELETE: Specifies that the trigger should be executed after a DELETE operation.
• on patrons: Indicates the table to which the trigger is applied, which is the "patrons" table in this case.
• FOR EACH ROW: Specifies that the trigger should be executed for each deleted row in the "patrons" table.
• BEGIN: Marks the start of the trigger body.
• DELETE FROM patron_info...: The actual deletion statement that removes the associated row from the "patron_info" table.
• WHERE patron_info.pid = old.id: This condition ensures that the specific row to be deleted is identified by matching the pid column value with the corresponding patron ID from the deleted row in the "patrons" table.
• END: Marks the end of the trigger body.

It is important to note that the correct position of the semicolon at the end of the DELETE statement is crucial for the trigger to execute properly. Additionally, when executing the trigger code in a console window, delimiters should be used to prevent syntax errors.