MySQL - After Update Trigger

Table of content

A Trigger is simply defined as a response to an event. In MySQL, a trigger is 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.

MySQL After Update Trigger

The After Update Trigger is a row-level trigger supported by the MySQL database. As its name suggests, the After Update Trigger is executed right after a value is updated in 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.

Once the After Update trigger is defined in MySQL, whenever an UPDATE statement is executed in the database, the value of a table is updated first followed by execution of the trigger set.

Syntax

Following is the syntax to create the AFTER UPDATE trigger in MySQL −

CREATE TRIGGER trigger_name
AFTER UPDATE ON table_name FOR EACH ROW
BEGIN
   -- trigger body
END;

Example

Let us first create a table named USERS containing the details of users of an application. Use the following CREATE TABLE query to do so −

CREATE TABLE USERS(
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(100) NOT NULL,
   AGE INT NOT NULL,
   BIRTHDATE VARCHAR(100),
   PRIMARY KEY(ID)
);

Insert values into the USERS table using the regular INSERT statement as shown below −

INSERT INTO USERS (NAME, AGE, BIRTHDATE) VALUES 
('Sasha', 23, '24/06/1999');
('Alex', 21, '12/01/2001');

The USERS table is created as follows −

ID NAME AGE BIRTHDATE
1 Sasha 23 24/06/1999
2 Alex 21 12/01/2001

Creating the trigger:

Using the following CREATE TRIGGER statement, create a new trigger 'after_update_trigger' on the USERS table to display a customized error using SQLSTATE as follows −

DELIMITER //
CREATE TRIGGER after_update_trigger AFTER UPDATE ON USERS FOR EACH ROW
BEGIN
IF NEW.AGE < 0 
THEN SIGNAL SQLSTATE '45000' 
SET MESSAGE_TEXT = 'Age Cannot be Negative';
END IF;
END //
DELIMITER ;

Update values of the SAMPLE table using the regular UPDATE statement as shown below −

UPDATE SAMPLE SET AGE = -1 WHERE NAME = 'Sasha';

Output

An error is displayed as the output for this query −

ERROR 1644 (45000): Age Cannot be Negative

After Update Trigger Using a Client Program

We can also execute the After Update Triggers in MySQL database using a client program instead of querying SQL statements directly.

Syntax

PHP NodeJS Java Python

To execute the After Update Trigger through a PHP program, we need to execute the CREATE TRIGGER statement using the mysqli function query() as follows −

$sql = "CREATE TRIGGER after_update_trigger AFTER UPDATE ON SAMPLE FOR EACH ROW
BEGIN
IF NEW.AGE < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Age Cannot be Negative';
END IF;
END ";
$mysqli->query($sql);

To execute the After Update Trigger through a JavaScript program, we need to execute the CREATE TRIGGER statement using the query() function of mysql2 library as follows −

sql = `CREATE TRIGGER after_update_trigger AFTER UPDATE ON SAMPLE FOR EACH ROW
BEGIN
IF NEW.AGE < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Age Cannot be Negative';
END IF;
END`;
con.query(sql);

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

String sql = "CREATE TRIGGER after_update_trigger AFTER UPDATE ON SAMPLE FOR EACH ROW BEGIN IF NEW.AGE < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Age Cannot be Negative';
END IF; 
END";
statement.execute(sql);

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

afterUpdate_trigger_query = 'CREATE TRIGGER {trigger_name}
AFTER UPDATE ON {table_name}
FOR EACH ROW
BEGIN
IF NEW.AGE < 0
THEN SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Age Cannot be Negative';
END IF
END'
cursorObj.execute(afterUpdate_trigger_query)

Example

Following are the programs −

PHP NodeJS Java Python 
$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_update_trigger AFTER UPDATE ON SAMPLE FOR EACH ROW
BEGIN
IF NEW.AGE < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Age Cannot be Negative';
END IF;
END ";
if($mysqli->query($sql)){
   printf("Trigger created successfully...!\n");
}
$q = "UPDATE SAMPLE SET AGE = -1 WHERE NAME = 'Sasha'";
$result = $mysqli->query($q);
if ($result == true) {
   printf("Record updated successfully...!\n");
}
if($mysqli->error){
   printf("Error message: " , $mysqli->error);
}
$mysqli->close();

Output

The output obtained is as follows −

Trigger created successfully...!
PHP Fatal error:  Uncaught mysqli_sql_exception: Age Cannot be Negative    

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_update_trigger AFTER UPDATE ON SAMPLE FOR EACH ROW
 BEGIN
 IF NEW.AGE < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Age Cannot be Negative';
 END IF;
 END`;
 con.query(sql);
 console.log("After Update query executed successfully..!");
 sql = "UPDATE SAMPLE SET AGE = -1 WHERE NAME = 'Sasha'";
 con.query(sql);
 console.log("Table records: ")
 sql = "SELECT * FROM Sample";
 con.query(sql, function(err, result){
 if (err) throw err;
 console.log(result);
 });
});

Output

The output produced is as follows −

Error: Age Cannot be Negative

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class AfterUpdateTrigger {
    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...!");
            String sql = "SELECT * FROM SAMPLE";
            rs = st.executeQuery(sql);
            System.out.println("Sample table records before update: ");
            while(rs.next()) {
                String id = rs.getString("id");
                String name = rs.getString("name");
                String age = rs.getString("age");
                String birth_date = rs.getString("BIRTHDATE");
                System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Birth_date: " + birth_date);
            }
            //lets create trigger on student table
            String sql1 = "CREATE TRIGGER after_update_trigger AFTER UPDATE ON SAMPLE FOR EACH ROW BEGIN IF NEW.AGE < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Age Cannot be Negative';
            END IF; 
            END";
            st.execute(sql1);
            System.out.println("Triggerd Created successfully...!");
            //let update the table records
            String sql3 = "UPDATE SAMPLE SET AGE = -1 WHERE NAME = 'Sasha'";
            st.execute(sql3);
            //let print SAMPLE table records
            String sql4 = "SELECT * FROM SAMPLE";
            rs = st.executeQuery(sql4);
            System.out.println("Sample table records after update: ");
            while(rs.next()) {
                String id = rs.getString("id");
                String name = rs.getString("name");
                String age = rs.getString("age");
                String birth_date = rs.getString("BIRTHDATE");
                System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Birth_date: " + birth_date);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}

Output

The output obtained is as shown below −

Sample table records before update: 
Id: 1, Name: Sasha, Age: 23, Birth_date: 24/06/1999
Id: 2, Name: Alex, Age: 21, Birth_date: 12/01/2001
Triggerd Created successfully...!
java.sql.SQLException: Age Cannot be Negative

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 = 'Sample'
trigger_name = 'after_update_trigger'
afterUpdate_trigger_query = f'''CREATE TRIGGER {trigger_name}
AFTER UPDATE ON {table_name}
FOR EACH ROW
BEGIN
IF NEW.AGE < 0
THEN SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Age Cannot be Negative';
END IF;
END'''
cursorObj.execute(afterUpdate_trigger_query)
print(f"AFTER UPDATE Trigger '{trigger_name}' is created successfully.")
connection.commit()
# Update the "AGE" column
update_query = "UPDATE Sample SET AGE = -1 WHERE NAME = 'Sasha'"
cursorObj.execute(update_query)
print("Update query executed successfully.")
# close the cursor and connection
connection.commit()
cursorObj.close()
connection.close()

Output

Following is the output of the above code −

AFTER UPDATE Trigger 'after_update_trigger' is created successfully.
Traceback (most recent call last):
  File "C:\Users\Lenovo\AppData\Local\Programs\Python\Python310\lib\site-packages\mysql\connector\connection_cext.py", line 633, in cmd_query
    self._cmysql.query(
_mysql_connector.MySQLInterfaceError: Age Cannot be Negative

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\Lenovo\Desktop\untitled.py", line 29, in 
    cursorObj.execute(update_query)
  File "C:\Users\Lenovo\AppData\Local\Programs\Python\Python310\lib\site-packages\mysql\connector\cursor_cext.py", line 330, in execute
    result = self._cnx.cmd_query(
  File "C:\Users\Lenovo\AppData\Local\Programs\Python\Python310\lib\site-packages\mysql\connector\opentelemetry\context_propagation.py", line 77, in wrapper
    return method(cnx, *args, **kwargs)
  File "C:\Users\Lenovo\AppData\Local\Programs\Python\Python310\lib\site-packages\mysql\connector\connection_cext.py", line 641, in cmd_query
    raise get_mysql_exception(
mysql.connector.errors.DatabaseError: 1644 (45000): Age Cannot be Negative
Advertisements