mysql

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

Posted on by user

MySQL – After Delete Trigger

Table of content

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

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

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)

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

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.

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