MySQL - Before Update Trigger

Table of content

Triggers in MySQL are of two types: Before Triggers and After Triggers for various SQL operations like insertion, deletion and update. As we have already learned in previous chapters, the After Update Trigger is executed immediately after a value is updated in a row of a database table. Here, let us learn more about BEFORE UPDATE trigger.

MySQL Before Update Trigger

The Before Update Trigger is a row-level trigger supported by the MySQL database. It is type of special stored procedure which is executed automatically before 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.

Whenever an UPDATE statement is executed in the database, the trigger is set to go off first followed by the updated value.

Syntax

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

CREATE TRIGGER trigger_name
BEFORE 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 'before_update_trigger' on the USERS table to display a customized error using SQLSTATE as follows −

DELIMITER //
CREATE TRIGGER before_update_trigger 
BEFORE 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 USERS table using the regular UPDATE statement −

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

Output

An error is displayed as the output for this query −

ERROR 1644 (45000): Age Cannot be Negative

Before Update Trigger Using a Client Program

We can also execute the Before Update Trigger using a client program instead of SQL queries directly.

Syntax

PHP NodeJS Java Python

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

$sql = "CREATE TRIGGER before_update_trigger BEFORE 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 Before 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 before_update_trigger BEFORE 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 Before 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 before_update_trigger BEFORE 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 Before 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 −

beforeUpdate_trigger_query = 'CREATE TRIGGER {trigger_name}
BEFORE 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'
cursorObj.execute(beforeUpdate_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 before_update_trigger BEFORE 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 before_update_trigger BEFORE 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("Before 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 BeforeUpdateTrigger {
    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 before_update_trigger BEFORE 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...!");
            //lets update 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  

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 = 'before_update_trigger'
beforeUpdate_trigger_query = f'''
CREATE TRIGGER {trigger_name}
BEFORE 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(beforeUpdate_trigger_query)
print(f"BEFORE 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 −

BEFORE UPDATE Trigger 'before_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