SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
MySQL Database Tutorials, Downloads, Guides and Articles for SQL Database Developers

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.

MySQL database triggers

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

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);

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
ON CustomerTransaction

update CustomerBalance
SET balance = balance + netamount * case when new.TransactionType = 'IN' then 1 else -1 end
where CustomerId = new.CustomerId;

end $$

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;

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;

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.

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.