How to create After Update Trigger in MySQL Method 1 - MySQL Developer Tutorial

Imagine you're working on a project as a MySQL Developer, and you've been asked to make sure all changes to records are properly tracked. This means keeping a record of the old values and the new values after an update. To achieve this, you can make use of an "After Update Trigger" in MySQL.

First, let's set up a sample table called "customer" and add some example data to it. This table will store customer information like their name, age, phone number, and more.

Now, let's create an "Audit" table named "customer_audit." This table will be used to store the old and new values of the records after an update occurs. We're adding additional columns to keep track of who performed the action and when it was done.

Creating the actual trigger involves a few steps. We'll start by defining a trigger named "customer_after_update" that activates after an update is made to the "customer" table.

Inside the trigger, we'll need to:

1. Declare a variable called "var_User" to store the username of the person making the update.

2. Use the "SELECT USER() INTO var_User" statement to assign the current user's name to the variable.

3. Insert the old and new values, along with the user's name and the current date and time, into the "customer_audit" table.

After setting up the trigger, you can test it out. Update a record in the "customer" table, and then check the "customer_audit" table to see if the changes have been successfully recorded.

And that's it! With this approach, you'll be able to keep a comprehensive record of any changes made to the customer records in the database. This can be quite handy for maintaining data integrity and tracking modifications over time.