MySQL Trigger After Insert on Database Table Sample SQL Code

MySQL trigger defined on a database table enables automatic SQL code execution after or before a DML (Insert, Update or Delete) command executed on a specific table. For example, SQL developer can update a parent table row after a data insert in a child table. MySQL trigger code provides the logical data consistency between database tables and also forces data integrity and quality by an automatically executed SQL code block. Using BEFORE triggers enable MySQL database developer to check and validate input data before inserting, updating or deleting the table row. AFTER triggers helps in general MySQL programmers to modify additional database tables based on the input data. In this MySQL tutorial, I want to share sample SQL codes for an AFTER INSERT trigger.

For our sample scenario, we assume that we are a financial firm and keep our customers' net balance in CustomerBalance table.
Every transaction created for the customer is kept in MySQL database table CustomerTransaction with TransactionType column indicating the incoming or outgoing amount for the transaction record.

Here is the following DDL (Data Definition Language) SQL code for table creation to use in this tutorial sample.


 Create Table CustomerBalance (

 CustomerId int,

 Balance float

 );

 

 Create Table CustomerTransaction (

 TransactionId int,

 CustomerId int,

 TransactionType varchar(10),

 Amount float

 );

Code

Let's now define a few customers by inserting new records in our CustomerBalance table with 0 balance.


 Insert Into CustomerBalance (CustomerId, Balance) values (1, 0);

 Insert Into CustomerBalance (CustomerId, Balance) values (2, 0);

 Insert Into CustomerBalance (CustomerId, Balance) values (3, 0);

Code

Now, by using triggers I want to update the customer balance amount everytime a new transaction record is entered.

So the MySQL database table trigger will be created for the customer transaction table which will execute after a new transaction record is inserted.
On CustomerTransation table, only insert SQL statements will be executed.
Assume that transaction data cannot be updated nor deleted because of regulations.
So MySQL database administrators should only create After Insert trigger to modify customer balance.


 delimiter $$

 CREATE TRIGGER UpdateCustomerBalance

 AFTER INSERT

 ON CustomerTransaction

 FOR EACH ROW

 BEGIN


 update CustomerBalance

 SET balance = balance + netamount * case when new.TransactionType = 'IN' then 1 else -1 end

 where CustomerId = new.CustomerId;


 end $$

Code

MySQL database After Insert Trigger sample SQL code

Let's now insert new rows into database table CustomerTransaction and check if the AFTER INSERT MySQL database table trigger will fire data update on CustomerBalance table


 Insert Into CustomerTransaction Select 1, 2, 'IN', 100;

 Insert Into CustomerTransaction Select 2, 2, 'OUT', 25;

 Insert Into CustomerTransaction Select 3, 3, 'IN', 30;

 Insert Into CustomerTransaction Select 4, 1, 'IN', 10;

 Insert Into CustomerTransaction Select 5, 1, 'IN', 40;

Code

Now we can check the CustomerBalance table if the net amount of each customer has been correctly updated after related transactions of the customer.


 select * from CustomerBalance limit 10;

Code

As can be seen, the AFTER INSERT MySQL database table trigger execution is successful and the master table, in our case CustomerBalance is automatically modified to display the latest data based on the child table CustomerTransaction transactions or INSERT DML statements.

MySQL database table updated automatically after insert trigger execution

I hope this SQL tutorial showing how to create AFTER INSERT MySQL database table trigger is useful and helps SQL developers for building their data applications.