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

If you're a MySQL developer and need to create a trigger that keeps track of updated records, here's a step-by-step explanation;

Step 1: Setting up the Environment

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.

Step 2: Creating a Sample Table

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.

Step 3: Defining an Audit Table

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.

Step 4: Crafting the Trigger

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.

Step 5: Testing the Trigger

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.
Next Post Previous Post
No Comment
Add Comment
comment url