mysql

Khóa học miễn phí MySQL – Before Delete Trigger nhận dự án làm có lương

Posted on by user

MySQL – Before Delete Trigger

Table of content

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

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

To execute the Before Delete Trigger through a JavaScript program, we need to query the CREATE TRIGGER statement using the query() function of mysql2 library 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`;
con.query(sql);

To execute the Before Delete Trigger through a Java program, we need to query the CREATE TRIGGER statement using the JDBC function execute() as follows −

String 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";
statement.execute(sql);

To execute the Before Delete Trigger through a python program, we need to query the CREATE TRIGGER statement using the execute() function of the MySQL Connector/Python as follows −

beforeDelete_trigger_query = ''CREATE TRIGGER {trigger_name}
BEFORE DELETE ON {table_name}
FOR EACH ROW
BEGIN
INSERT INTO {another_table} VALUES (OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY);
END''
cursorObj.execute(beforeDelete_trigger_query)

Example

Following are the programs −

$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      

var mysql = require(''mysql2'');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});

 //Connecting to MySQL
 con.connect(function(err) {
 if (err) throw err;
  //console.log("Connected successfully...!");
  //console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 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`;
 con.query(sql);
 console.log("Before delete query executed successfully..!");
 sql = "DELETE FROM CUSTOMERS WHERE ID = 3";
 console.log("Delete query executed successfully...!");
 con.query(sql);
 console.log("Customers table records: ")
 sql = "SELECT * FROM CUSTOMERS";
 con.query(sql, function(err, result){
 if (err) throw err;
 console.log(result);
 console.log(''---------------------------------'');
 console.log(''OLD_CUSTOMERS table records: '')
 });
 sql = "SELECT * FROM OLD_CUSTOMERS";
 con.query(sql, function(err, result){
 if (err) throw err;
 console.log(result);
 });
});

Output

The output produced is as follows −

After delete query executed successfully..!
Delete query executed successfully...!
Customers table records:
[
  {
    ID: 1,
    NAME: ''Ramesh'',
    AGE: 32,
    ADDRESS: ''Ahmedabad'',
    SALARY: ''2000.00''
  },
  {
    ID: 2,
    NAME: ''Khilan'',
    AGE: 25,
    ADDRESS: ''Delhi'',
    SALARY: ''1500.00''
  },
  {
    ID: 4,
    NAME: ''Chaitali'',
    AGE: 25,
    ADDRESS: ''Mumbai'',
    SALARY: ''6500.00''
  },
  {
    ID: 5,
    NAME: ''Hardik'',
    AGE: 27,
    ADDRESS: ''Bhopal'',
    SALARY: ''8500.00''
  },
  { ID: 6, NAME: ''Komal'', AGE: 22, ADDRESS: ''MP'', SALARY: ''4500.00'' },
  {
    ID: 7,
    NAME: ''Muffy'',
    AGE: 24,
    ADDRESS: ''Indore'',
    SALARY: ''10000.00''
  }
]
---------------------------------
OLD_CUSTOMERS table records:
[
  {
    ID: 3,
    NAME: ''kaushik'',
    AGE: 23,
    ADDRESS: ''Kota'',
    SALARY: ''2000.00''
  }
]    

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class BeforeDeleteTrigger {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String user = "root";
        String password = "password";
        ResultSet rs;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
             //create table
            String sql = "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))";
            st.execute(sql);
            System.out.println("Customers table created successfully...!");
            //lets insert some records into customers table
            String sql1 = "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 )";
            st.execute(sql1);
            System.out.println("Records inserted successfully...!");
            //print table records
            String sql2 = "SELECT * FROM CUSTOMERS";
            rs = st.executeQuery(sql2);
            System.out.println("Customers table records: ");
            while(rs.next()) {
                String id = rs.getString("id");
                String name = rs.getString("name");
                String age = rs.getString("age");
                String address = rs.getString("address");
                String salary = rs.getString("salary");
                System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Address: " + address + ", Salary: " + salary);
            }
            //let create one more table named Old_customers
            String sql3 = "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))";
            st.execute(sql3);
            System.out.println("OLD_CUSTOMERS table created successully...!");
            //print the records
            String sql4 = "SELECT * FROM OLD_CUSTOMERS";
            rs = st.executeQuery(sql4);
            System.out.println("OLD_CUSTOMERS table records before delete trigger: ");
            while(rs.next()) {
                String id = rs.getString("id");
                String name = rs.getString("name");
                String age = rs.getString("age");
                String address = rs.getString("address");
                String salary = rs.getString("salary");
                System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Address: " + address + ", Salary: " + salary);
            }
            //lets create trigger on student table
            String sql5 = "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";
            st.execute(sql5);
            System.out.println("Triggerd Created successfully...!");
            //lets delete one record from customers table;
            String d_sql = "DELETE FROM CUSTOMERS WHERE ID = 3";
            st.execute(d_sql);
            System.out.println("Record with id = 3 deleted successfully....!");
            //let print OLD_CUSTOMERS table records
            String sql6 = "SELECT * FROM OLD_CUSTOMERS";
            rs = st.executeQuery(sql6);
            System.out.println("OLD_CUSTOMERS records: ");
            while(rs.next()) {
                String id = rs.getString("id");
                String name = rs.getString("name");
                String age = rs.getString("age");
                String address = rs.getString("address");
                String salary = rs.getString("salary");
                System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Address: " + address + ", Salary: " + salary);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}

Output

The output obtained is as shown below −

Customers table created successfully...!
Records inserted successfully...!
Customers table records: 
Id: 1, Name: Ramesh, Age: 32, Address: Ahmedabad, Salary: 2000.00
Id: 2, Name: Khilan, Age: 25, Address: Delhi, Salary: 1500.00
Id: 3, Name: kaushik, Age: 23, Address: Kota, Salary: 2000.00
Id: 4, Name: Chaitali, Age: 25, Address: Mumbai, Salary: 6500.00
Id: 5, Name: Hardik, Age: 27, Address: Bhopal, Salary: 8500.00
Id: 6, Name: Komal, Age: 22, Address: MP, Salary: 4500.00
Id: 7, Name: Muffy, Age: 24, Address: Indore, Salary: 10000.00
OLD_CUSTOMERS table created successully...!
OLD_CUSTOMERS table records before delete trigger: 
Triggerd Created successfully...!
Record with id = 3 deleted successfully....!
OLD_CUSTOMERS records: 
Id: 3, Name: kaushik, Age: 23, Address: Kota, Salary: 2000.00    

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
table_name = ''Customers''
another_table = ''OLD_CUSTOMERS''
trigger_name = ''before_delete_trigger''
beforeDelete_trigger_query = f''''''
CREATE TRIGGER {trigger_name}
BEFORE DELETE ON {table_name}
FOR EACH ROW
BEGIN
INSERT INTO {another_table} VALUES (OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY);
END
''''''
cursorObj.execute(beforeDelete_trigger_query)
print(f"BEFORE DELETE Trigger ''{trigger_name}'' is created successfully.")
connection.commit()
# Delete details of old customer
delete_query = "DELETE FROM Customers WHERE ID = 3;"
cursorObj.execute(delete_query)
print("Delete query executed successfully.")
# close the cursor and connection
connection.commit()
cursorObj.close()
connection.close()

Output

Following is the output of the above code −

BEFORE DELETE Trigger ''before_delete_trigger'' is created successfully.
Delete query executed successfully.

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

user