How to Correctly Delete Records from a Dependent Table After Deleting from the Main Table in MySQL?

MySQL Trigger: Delete Records from Dependent Table After Deletion in Main Table

In a database schema, it is common to have multiple tables with relationships between them. When records are manipulated in the main table, it may be necessary to also perform corresponding actions on dependent tables. MySQL triggers provide a convenient way to automate such operations.

Consider a scenario where a table called patrons stores information about individuals, and a related table called patron_info contains additional details for each patron. When a patron is removed from the patrons table, it is desirable to also delete their corresponding information from the patron_info table.

To achieve this, a DELETE trigger can be employed. However, a syntax error was encountered when trying to create the trigger using the code:

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

The error (1046: Check syntax near END on line 6) indicates an issue with the syntax of the trigger code. Upon closer examination, it is發現 that the old.id syntax should be used instead of patrons.id within the DELETE statement to correctly reference the ID of the deleted patron.

The corrected trigger code is as follows:

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

It is important to note that the use of delimiters (such as $ or //) may be necessary when entering the trigger code in a console window to handle specific characters in the code. Additionally, do not forget to include a semicolon at the end of the DELETE statement.

By following these guidelines, the trigger will successfully delete records from the patron_info table whenever a record is deleted from the patrons table, ensuring data consistency and integrity within the database.

The above is the detailed content of How to Correctly Delete Records from a Dependent Table After Deleting from the Main Table in MySQL?. For more information, please follow other related articles on the PHP Chinese website!