MySQL - After Delete Trigger

To execute the After 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 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`;
con.query(sql);  

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

To execute the After 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 −

afterDelete_trigger_query = 'CREATE TRIGGER {trigger_name}
AFTER 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(afterDelete_trigger_query)

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 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`;
 con.query(sql);
 console.log("After delete query executed successfully..!");
 sql = "DELETE FROM CUSTOMERS WHERE ID = 3";
 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..!
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 AfterDeleteTrigger {
    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 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";
            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 = 'after_delete_trigger'
afterDelete_trigger_query = f'''
CREATE TRIGGER {trigger_name}
AFTER 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(afterDelete_trigger_query)
print(f"AFTER 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 −

AFTER DELETE Trigger 'after_delete_trigger' is created successfully.
Delete query executed successfully.