Using MySQL Trigger: Automatic Data Processing with BEFORE and AFTER Triggers

💡 What Will You Learn in This Guide?

In this guide, you will learn how to create MySQL triggers, in what situations they are used and how they protect data integrity.
Step by step we will do the following:

• Understanding the differences between $BEFORE$ and $AFTER$ triggers,
• Automating $INSERT$, $UPDATE$ and $DELETE$ operations,
• Archiving deleted records,
• Dynamically updating summary (statistics) tables.

🔍 In short: This guide teaches you how to do automatic data management without dealing with manual queries.

🧠 What is MySQL Trigger?

A trigger is an SQL function that automatically runs when a specific event occurs (for example, a row is inserted, updated, or deleted).
In this way, you can ensure that the system manages itself after every user action.

🎯 Intended Use

• Maintaining data consistency
• Archiving deleted records
• Keeping statistical data up to date
• Creating an audit trail

For example:
When a product is deleted, it can be automatically saved in the “deleted products” table or the name of newly added records can be automatically converted to uppercase.

---

🔧 Prerequisites

To follow these steps you need:

1. MySQL server (e.g. a virtual server on GenixNode)
2. Basic command of $SELECT$, $INSERT$, $UPDATE$, $DELETE$ queries
3. A MySQL user with administrative rights

To connect to MySQL:

mysql -u kullanici_adiniz -p

---

🗃️ Sample Database and Tables

These examples will use a database called mycollection.

CREATE DATABASE koleksiyonum;
USE koleksiyonum;

Main tables:

1️⃣ Products Table

Kodu kopyala
CREATE TABLE urunler (
    ad VARCHAR(50),
    deger DECIMAL(5,2)
);

---

2️⃣ Statistics Table

CREATE TABLE urunler_istatistik (
    sayi INT,
    deger DECIMAL(5,2)
);
INSERT INTO urunler_istatistik VALUES (0, 0.00);

---

3️⃣ Archive Table

CREATE TABLE urunler_arsiv (
    ad VARCHAR(50),
    deger DECIMAL(5,2),
    silinme_tarihi TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

📌 With this structure, you can add and delete products to your collection and manage statistics automatically.

---

🧩 Trigger Logic and Structure

The general syntax is:

CREATE TRIGGER tetikleyici_adi
[BEFORE | AFTER] [INSERT | UPDATE | DELETE]
ON tablo_adi
FOR EACH ROW
<yapilacak_islem>;

Description:

Keyword	Description
BEFORE / AFTER	Works before or after the process
INSERT, UPDATE, DELETE	Event to trigger
NEW	Added or updated data
OLD	Data before deleted or changed

---

✍️ BEFORE INSERT / UPDATE — Ensuring Data Consistency

The following triggers automatically convert product names to uppercase.

CREATE TRIGGER buyuk_harf_insert
BEFORE INSERT ON urunler
FOR EACH ROW
SET NEW.ad = UPPER(NEW.ad);
$BEFORE$ $UPDATE$

CREATE TRIGGER buyuk_harf_update
BEFORE UPDATE ON urunler
FOR EACH ROW
SET NEW.ad = UPPER(NEW.ad);

🧠 In this way, even if 'model airplane' is entered, it is recorded in the table as 'MODEL AIRPLANE'.

---

🗑️ BEFORE DELETE — Archiving Deleted Data

To save the record in a backup table just before it is deleted:

CREATE TRIGGER arsivle_delete
BEFORE DELETE ON urunler
FOR EACH ROW
INSERT INTO urunler_arsiv (ad, deger)
VALUES (OLD.ad, OLD.deger);

💾 This process ensures that deleted records are stored in the products_archive table.

---

📊 AFTER INSERT / UPDATE / DELETE — Automatic Statistics Update

Three triggers are created to automatically update summary information after each transaction:

CREATE TRIGGER istatistik_after_insert
AFTER INSERT ON urunler
FOR EACH ROW
UPDATE urunler_istatistik
SET sayi = (SELECT COUNT(ad) FROM urunler),
    deger = (SELECT SUM(deger) FROM urunler);

CREATE TRIGGER istatistik_after_update
AFTER UPDATE ON urunler
FOR EACH ROW
UPDATE urunler_istatistik
SET sayi = (SELECT COUNT(ad) FROM urunler),
    deger = (SELECT SUM(deger) FROM urunler);

CREATE TRIGGER istatistik_after_delete
AFTER DELETE ON urunler
FOR EACH ROW
UPDATE urunler_istatistik
SET sayi = (SELECT COUNT(ad) FROM urunler),
    deger = (SELECT SUM(deger) FROM urunler);

📈 Thanks to these triggers, the number of tables and their total value always remain up to date.

🧾 Viewing and Deleting Triggers

Listing:

SHOW TRIGGERS;

Delete:

DROP TRIGGER buyuk_harf_insert;
DROP TRIGGER buyuk_harf_update;

🧹 These commands disable the relevant triggers.

💬 Frequently Asked Questions (FAQ)

1️⃣ What is the difference between $OLD$ and $NEW$? $OLD$ represents the old data (before $UPDATE$ / $DELETE$), $NEW$ represents the new value (after $INSERT$ / $UPDATE$).

2️⃣ Can a trigger run multiple SQL commands? Yes, you can define multiple queries with $BEGIN$ and $END$ blocks.

3️⃣ Do triggers affect performance? It may create a small load on tables that are triggered very frequently. Optimize your queries.

4️⃣ What is the difference between Trigger and View? Views only display the data, while triggers directly intervene in the data.

5️⃣ In what situations should I not use triggers? In systems with heavy write traffic (e.g. log tables) it is best to keep the number of triggers to a minimum.

---

🏁 Result

MySQL triggers are powerful tools for database automation and data integrity. With this guide, you can reduce manual processing errors and make your database smarter.

☁️ You can try trigger examples on GenixNode MySQL servers immediately and take the reliability of your system to the next level.