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.

Before Delete Trigger
In MySQL, a trigger is defined a special stored procedure that resides in the system catalogue, and is executed whenever an event is performed. It 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
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 Before Delete Trigger
The Before Delete Trigger is a row-level trigger supported by the MySQL database. The Before Delete Trigger is executed right before a value is deleted from a row of a database table.
A row-level trigger is a type of trigger that goes off every time a row is modified. Simply, for every single transaction made in a table (like insertion, deletion, update), one trigger acts automatically.
With this trigger, whenever a DELETE statement is executed in the database, the value is deleted from a table first followed by execution of the trigger set.
Syntax
Following is the syntax to create the BEFORE DELETE trigger in MySQL −
CREATE TRIGGER trigger_name
BEFORE 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 BEFORE 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 'before_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 before_delete_trigger 
BEFORE 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 OCUSTOMERS table and added onto the OLD_CUSTOMERS table, let us try to retrieve both of their result-sets using 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.
Before Delete Trigger Using Client Program
We can also execute the Before Delete trigger statement using a client program, instead of SQL queries.
Syntax
PHPNodeJSJavaPython
To execute the Before Delete Trigger through a PHP program, we need to query the CREATE TRIGGER statement using the mysqli function query() as follows −
$sql = "CREATE TRIGGER before_delete_trigger BEFORE 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 before_delete_trigger BEFORE 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

Before Delete Trigger

MySQL Before Delete Trigger

Syntax

Example

Verification

Before Delete Trigger Using Client Program

Syntax

Example

Output