Creating a Trigger in MySQL to Update the 'deleted_at' Column on Row Modification

Learn how to create a MySQL trigger to automatically update the 'deleted_at' column whenever a row is modified. Step-by-step guide for effective SQL update triggers.
---
Disclaimer/Disclosure: Some of the content was synthetically produced using various Generative AI (artificial intelligence) tools; so, there may be inaccuracies or misleading information present in the video. Please consider this before relying on the content to make any decisions or take any actions etc. If you still have any concerns, please feel free to write them in a comment. Thank you.
---
Creating a Trigger in MySQL to Update the 'deleted_at' Column on Row Modification

Managing data integrity and maintaining accurate records in a database is crucial for many applications. One common task is to keep track of deletions or modifications, especially in systems that utilize soft deletes. This guide explains how to correctly create a MySQL trigger to update the deleted_at column every time a row is modified.

What is a MySQL Trigger?

A MySQL trigger is a stored program that is automatically invoked by the MySQL server whenever a specified event occurs. Triggers can be activated for various SQL operations such as INSERT, UPDATE, and DELETE.

Creating a Trigger on UPDATE

To create a MySQL trigger that updates the deleted_at column on row modification, follow these steps:

Setting Up the Table:

Ensure your table has a deleted_at column, preferably of the type DATETIME to store timestamp information.

[[See Video to Reveal this Text or Code Snippet]]

Creating the Trigger:

Use the CREATE TRIGGER statement to define the trigger. In this case, we want the trigger to fire before an UPDATE operation, ensuring that the deleted_at column gets updated accordingly.

[[See Video to Reveal this Text or Code Snippet]]

DELIMITER Statement: Changing the delimiter to // allows us to use the ; symbol within the trigger body.

Trigger Syntax:

CREATE TRIGGER before_update_my_table defines the trigger.

BEFORE UPDATE ON my_table specifies that the trigger activates before an UPDATE operation on my_table.

FOR EACH ROW indicates that the trigger operates on each row affected by the UPDATE.

The BEGIN...END block contains the trigger logic, setting NEW.deleted_at to the current timestamp using NOW().

Trigger in Action:

When you update any row in my_table, the before_update_my_table trigger automatically updates the deleted_at column to the current date and time.

[[See Video to Reveal this Text or Code Snippet]]

The before_update_my_table trigger will run, and the deleted_at column for the row where id = 1 will be updated to the current timestamp.

Conclusion

Creating a MySQL trigger for updating the deleted_at column ensures that you maintain a historical record of when rows are modified. This can be particularly useful for audit trails, soft deletion strategies, and maintaining data integrity.

By following the above steps, you can seamlessly integrate this functionality into your MySQL tables, enhancing the robustness and reliability of your database operations.