MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

After Delete Trigger
In general, a Trigger is defined as a response to an event. In MySQL, a trigger is called a special stored procedure as it does not require to be invoked explicitly like other stored procedures. The trigger acts automatically whenever the desired event is fired. Triggers are categorized into two types: Before Triggers and After Triggers.
These triggers can be a response to either insertion operation on a table, update operation or deletion operation. Thus, these special stored procedures respond whenever INSERT, UPDATE or DELETE statements are executed.
MySQL After Delete Trigger
The After Delete Trigger is a row-level trigger supported by the MySQL database. This trigger is executed right after a value is deleted from a row of a database table.
A row-level trigger is a type of trigger that is executed every time a row is modified. For every single transaction made in a table (like insertion, deletion, update operation), one trigger acts automatically.
When a DELETE statement is executed in the database, the trigger is performed first and then the said value is deleted from the table.
Syntax
Following is the syntax to create the AFTER DELETE trigger in MySQL −
CREATE TRIGGER trigger_name
AFTER DELETE ON table_name FOR EACH ROW
BEGIN
   -- trigger body
END;
﻿
Example
In this example, we are creating a table named 'CUSTOMERS', to demonstrate the AFTER DELETE trigger on, using the following query −
CREATE TABLE CUSTOMERS(
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS VARCHAR(25),
   SALARY DECIMAL(18, 2),
   PRIMARY KEY(ID)
);
﻿
Insert values into this table created using the following INSERT statements −
INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'MP', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );
﻿
Creating Another Table:
Now, let us create another empty table to store all former customers after being deleted from the main table 'CUSTOMERS' −
CREATE TABLE OLD_CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS VARCHAR(25),
   SALARY DECIMAL(18, 2),
   PRIMARY KEY(ID)
);
﻿
Using the following CREATE TRIGGER statement, create a new trigger 'after_delete_trigger' on the CUSTOMERS table to delete the customer details from CUSTOMERS table and insert them into another table "OLD_CUSTOMERS" −
DELIMITER //
CREATE TRIGGER after_delete_trigger 
AFTER DELETE ON CUSTOMERS 
FOR EACH ROW
BEGIN
 INSERT INTO OLD_CUSTOMERS VALUES
 (OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY);
END //
DELIMITER ;
﻿
Delete details of 'old' customers from the CUSTOMERS table using the regular DELETE statement as shown below −
DELETE FROM CUSTOMERS WHERE ID = 3;
﻿
Verification
To verify whether the details are deleted from the CUSTOMERS table and added onto the OLD_CUSTOMERS table, let us try to retrieve both of their result-sets using the SELECT queries.
The records in CUSTOMERS table are as follows −
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
4
Chaitali
25
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
6
Komal
22
Hyderabad
4500.00
7
Muffy
24
Indore
10000.00
The records in OLD_CUSTOMERS table are as follows −
ID
NAME
AGE
ADDRESS
SALARY
3
Kaushik
23
Kota
2000.00
As you can in the tables above, the data has been deleted from the CUSTOMERS table and added to the OLD_CUSTOMERS table. However, the only difference that is not visible on the application level is that the trigger is performed after the deletion is done, in contrast to the BEFORE DELETE trigger.
After Delete Trigger Using a Client Program
We can also execute the After Delete trigger statement using a client program, instead of SQL queries.
Syntax
PHPNodeJSJavaPython
To execute the After Delete Trigger through a PHP program, we need to query the CREATE TRIGGER statement using the mysqli function query() as follows −
$sql = "CREATE TRIGGER after_delete_trigger AFTER DELETE ON CUSTOMERS FOR EACH ROW
BEGIN
INSERT INTO OLD_CUSTOMERS VALUES (OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY);
END";
$mysqli->query($sql);
﻿
Example
Following are the programs −
PHPNodeJSJavaPython
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$db = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s", $mysqli->connect_error);
    exit();
}
//printf('Connected successfully.');
$sql = "CREATE TRIGGER after_delete_trigger AFTER DELETE ON CUSTOMERS FOR EACH ROW
BEGIN
INSERT INTO OLD_CUSTOMERS VALUES (OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY);
END";
if ($mysqli->query($sql)) {
    printf("Trigger created successfully...!\n");
}
$q = "DELETE FROM CUSTOMERS WHERE ID = 3";
$result = $mysqli->query($q);
if ($result == true) {
    printf("Delete query executed successfully ...!\n");
}
$q1 = "SELECT * FROM CUSTOMERS";
$res1 = $mysqli->query($q1);
if ($res1->num_rows > 0) {
    printf("SELECT * FROM CUSTOMERS(verification): \n");
    while ($r1 = $res1->fetch_assoc()) {
        printf(
            "Id %d, Name: %s, Age: %d, Address %s, Salary %f",
            $r1['ID'],
            $r1["NAME"],
            $r1['AGE'],
            $r1["ADDRESS"],
            $r1["SALARY"],
        );
        printf("\n");
    }
}
$q2 = "SELECT * FROM OLD_CUSTOMERS";
$res2 = $mysqli->query($q2);
if ($res2->num_rows > 0) {
    printf("SELECT * FROM OLD_CUSTOMER(verification): \n");
    while ($r1 = $res2->fetch_assoc()) {
        printf(
            "Id %d, Name: %s, Age: %d, Address %s, Salary %f",
            $r1['ID'],
            $r1["NAME"],
            $r1['AGE'],
            $r1["ADDRESS"],
            $r1["SALARY"],
        );
        printf("\n");
    }
}
if ($mysqli->error) {
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();
﻿
Output
The output obtained is as follows −
Trigger created successfully...!
Delete query executed successfully ...!
SELECT * FROM CUSTOMERS(verification):
Id 1, Name: Ramesh, Age: 32, Address Ahmedabad, Salary 2000.000000
Id 2, Name: Khilan, Age: 25, Address Delhi, Salary 1500.000000
Id 4, Name: Chaitali, Age: 25, Address Mumbai, Salary 6500.000000
Id 5, Name: Hardik, Age: 27, Address Bhopal, Salary 8500.000000
Id 6, Name: Komal, Age: 22, Address MP, Salary 4500.000000
Id 7, Name: Muffy, Age: 24, Address Indore, Salary 10000.000000
SELECT * FROM OLD_CUSTOMER(verification):
Id 3, Name: Kaushik, Age: 23, Address Kota, Salary 2000.000000         
﻿
﻿
﻿
﻿
﻿
﻿

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

After Delete Trigger

MySQL After Delete Trigger

Syntax

Example

Verification

After Delete Trigger Using a Client Program

Syntax

Example

Output