How To Create Trigger That Make Changes To The Same Table

Summary: in this tutorial, yous will acquire how to create a MySQL Subsequently UPDATE trigger to log the changes made to a table.

Introduction to MySQL `Subsequently UPDATE` triggers

MySQL Later on UPDATE triggers are invoked automatically after an update event occurs on the table associated with the triggers.

The following shows the syntax of creating a MySQL AFTER UPDATE trigger:

CREATE TRIGGER trigger_name After UPDATE ON table_name FOR EACH ROW trigger_body

Lawmaking language: SQL (Structured Query Language) ( sql )

In this syntax:

First, specify the proper name of the trigger that you want to create in the CREATE TRIGGER clause.

Second, utilize Later UPDATE clause to specify the fourth dimension to invoke the trigger.

Third, specify the proper name of the table to which the trigger belongs later the ON keyword.

Finally, specify the trigger body which consists of 1 or more statements.

If the trigger body has more 1 statement, you need to use the BEGIN End block. And, you also need to modify the default delimiter equally shown in the following code:

DELIMITER $$ CREATE TRIGGER trigger_name AFTER UPDATE ON table_name FOR EACH ROW Begin -- statements END$$ DELIMITER ;

Code language: SQL (Structured Query Language) ( sql )

In a AFTER UPDATE trigger, you tin can admission OLD and NEW rows but cannot update them.

MySQL `Afterward UPDATE` trigger case

Let's await at an example of creating a AFTER UPDATE trigger.

Setting upwards a sample table

First, create a table called Sales:

Driblet Tabular array IF EXISTS Sales; CREATE Tabular array Sales ( id INT AUTO_INCREMENT, production VARCHAR(100) NOT NULL, quantity INT Not Nada DEFAULT 0, fiscalYear SMALLINT Non NULL, fiscalMonth TINYINT Not NULL, CHECK(fiscalMonth >= 1 AND fiscalMonth <= 12), CHECK(fiscalYear Betwixt 2000 and 2050), CHECK (quantity >=0), UNIQUE(product, fiscalYear, fiscalMonth), PRIMARY Cardinal(id) );

Code language: SQL (Structured Query Language) ( sql )

Second, insert sample data into the Sales table:

                                          INSERT                INTO                Sales(product, quantity, fiscalYear, fiscalMonth)                VALUES                ('2001 Ferrari Enzo',140,                2021,one),     ('1998 Chrysler Plymouth Prowler',                110,2021,one),     ('1913 Ford Model T Speedster',                120,2021,1);                          
                          Lawmaking language:              SQL (Structured Query Language)              (              sql              )

3rd, query data from the Sales table to display its contents:

                                          SELECT                *                FROM                Sales;                          
                          Code linguistic communication:              SQL (Structured Query Linguistic communication)              (              sql              )

MySQL AFTER UPDATE trigger - sample table

Finally, create a table that stores the changes in the quantity column from the sales table:

DROP Table IF EXISTS SalesChanges; CREATE Tabular array SalesChanges ( id INT AUTO_INCREMENT PRIMARY KEY, salesId INT, beforeQuantity INT, afterQuantity INT, changedAt TIMESTAMP Non NULL DEFAULT CURRENT_TIMESTAMP );

Lawmaking language: SQL (Structured Query Language) ( sql )

Creating `AFTER UPDATE` trigger example

The following statement creates an Afterward UPDATE trigger on the sales table:

                          DELIMITER $$                CREATE                TRIGGER                after_sales_update                AFTER                UPDATE                ON                sales                FOR                EACH                ROW                Brainstorm                IF                Onetime.quantity <> new.quantity                So                INSERT                INTO                SalesChanges(salesId,beforeQuantity, afterQuantity)                VALUES(erstwhile.id, old.quantity, new.quantity);                Cease                IF;                END$$  DELIMITER ;                          
                          Code language:              SQL (Structured Query Language)              (              sql              )

This after_sales_update trigger is automatically fired before an update event occurs for each row in the sales tabular array.

If y'all update the value in the quantity column to a new value the trigger insert a new row to log the changes in the SalesChanges table.

Let's examine the trigger in detail:

Starting time, the name of the trigger is after_sales_update specified in the CREATE TRIGGER clause:

CREATE TRIGGER after_sales_update

Code language: SQL (Structured Query Language) ( sql )

2nd, the triggering issue is:

AFTER UPDATE

Lawmaking language: SQL (Structured Query Language) ( sql )

Third, the tabular array that the trigger associated with is sales:

ON Sales FOR EACH ROW

Lawmaking language: SQL (Structured Query Language) ( sql )

Finally, use the IF-And so argument inside the trigger body to bank check if the new value is non the same equally the old one, then insert the changes into the SalesChanges table:

                          IF Erstwhile.quantity <> new.quantity THEN                INSERT                INTO                SalesChanges(salesId,beforeQuantity, afterQuantity)                VALUES(old.id, former.quantity, new.quantity);                End                IF;                          
                          Code linguistic communication:              SQL (Structured Query Language)              (              sql              )

Testing the MySQL `AFTER UPDATE` trigger

First, update the quantity of the row with id 1 to 350:

UPDATE Sales SET quantity = 350 WHERE id = i;

Code language: SQL (Structured Query Language) ( sql )

The after_sales_update was invoked automatically.

Second, query data from the SalesChanges table:

                                          SELECT                *                FROM                SalesChanges;                          
                          Code language:              SQL (Structured Query Language)              (              sql              )

Third, increase the sales quantity of all rows to 10%:

UPDATE Sales Prepare quantity = Cast(quantity * one.ane Equally UNSIGNED);

Code language: SQL (Structured Query Language) ( sql )

Fourth, query information from the SalesChanges table:

                                          SELECT                *                FROM                SalesChanges;                          
                          Code linguistic communication:              SQL (Structured Query Language)              (              sql              )

The trigger fired three times because of the updates of the three rows.

In this tutorial, you have learned how to create a MySQL AFTER UPDATE trigger to validate data before it is updated to a table.

Was this tutorial helpful?