MySQL - Before Insert Trigger

Table of content

As we have already learned, a Trigger is defined as a response to an event performed. 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. These events include executing SQL statements like INSERT, UPDATE and DELETE etc.

MySQL Before Insert Trigger

The Before Insert Trigger is a row-level trigger supported by the MySQL database. As its name suggests, this trigger is executed right before a value is being inserted into 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 INSERT statement is queried in the database, this Trigger is automatically executed first and then only the value is inserted into the table.

Syntax

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

CREATE TRIGGER trigger_name
BEFORE INSERT ON table_name FOR EACH ROW
BEGIN
   -- trigger body
END;

Example

Let us see an example demonstrating the BEFORE INSERT trigger. In here, we are creating a new table STUDENT which contains the details of students in an institution, using the following query −

CREATE TABLE STUDENT(
   Name varchar(35),
   Age INT,
   Score INT,
   Grade CHAR(10)
);

Using the following CREATE TRIGGER statement, create a new trigger sample_trigger on the STUDENT table. Here, we are checking the score of each student and assigning them with a suitable grade.

DELIMITER //
CREATE TRIGGER sample_trigger 
BEFORE INSERT ON STUDENT FOR EACH ROW
BEGIN
IF NEW.Score < 35 THEN SET NEW.Grade = 'FAIL';
ELSE SET NEW.Grade = 'PASS';
END IF;
END //
DELIMITER ;

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

INSERT INTO STUDENT VALUES
('John', 21, 76, NULL),
('Jane', 20, 24, NULL),
('Rob', 21, 57, NULL),
('Albert', 19, 87, NULL);

Verification

To verify if the trigger has been executed, display the STUDENT table using the SELECT statement −

Name Age Score Grade
John 21 76 PASS
Jane 20 24 FAIL
Rob 21 57 PASS
Albert 19 87 PASS

Before Insert Trigger Using a Client Program

In addition to create or show a trigger, we can also Perform the "Before Insert trigger" statement using a client program.

Syntax

PHP NodeJS Java Python

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

$sql = "Create Trigger sample_trigger BEFORE INSERT ON STUDENT"."
FOR EACH ROW
BEGIN
IF NEW.Score < 35 THEN SET NEW.Grade = 'FAIL';
ELSE SET NEW.Grade = 'PASS';
END IF;
END";
$mysqli->query($sql);

To Perform the Before Insert 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 sample_trigger BEFORE INSERT ON STUDENT 
FOR EACH ROW
BEGIN
IF NEW.Score < 35 THEN SET NEW.Grade = 'FAIL';
ELSE SET NEW.Grade = 'PASS';
END IF;
END`;
con.query(sql);

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

String sql = "Create Trigger sample_trigger BEFORE INSERT ON STUDENT FOR EACH ROW BEGIN IF NEW.Score < 35 THEN SET NEW.Grade = 'FAIL';
ELSE SET NEW.Grade = 'PASS';
END IF;
END";
statement.execute(sql);

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

beforeInsert_trigger_query = 'CREATE TRIGGER sample_trigger
BEFORE INSERT ON student
FOR EACH ROW
BEGIN
IF NEW.Score < 35
THEN SET NEW.Grade = 'FAIL';
ELSE SET NEW.Grade = 'PASS';
END IF;
END'
cursorObj.execute(drop_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 sample_trigger BEFORE INSERT ON STUDENT"."
   FOR EACH ROW
   BEGIN
   IF NEW.Score < 35 THEN SET NEW.Grade = 'FAIL';
   ELSE SET NEW.Grade = 'PASS';
   END IF;
   END";
   if($mysqli->query($sql)){
      printf("Trigger created successfully...!\n");
   }
   $q = "INSERT INTO STUDENT VALUES ('John', 21, 76, NULL)";
   $result = $mysqli->query($q);
   if ($result == true) {
      printf("Record inserted successfully...!\n");
   }
   $q1 = "SELECT * FROM STUDENT";
   if($r = $mysqli->query($q1)){
      printf("Select query executed successfully...!");
      printf("Table records(Verification): \n");
      while($row = $r->fetch_assoc()){
          printf("Name: %s, Age: %d, Score %d, Grade %s",   
         $row["Name"],
         $row["Age"],
         $row["Score"],
         $row["Grade"]);
         printf("\n");  
      }
  }
   if($mysqli->error){
      printf("Failed..!" , $mysqli->error);
   }
   $mysqli->close();

Output

The output obtained is as follows −

Trigger created successfully...!
Record inserted successfully...!
Select query executed successfully...!Table records(Verification):
Name: Jane, Age: 20, Score 24, Grade FAIL
Name: John, Age: 21, Score 76, Grade PASS   

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 sample_trigger BEFORE INSERT ON STUDENT 
 FOR EACH ROW
 BEGIN
 IF NEW.Score < 35 THEN SET NEW.Grade = 'FAIL';
 ELSE SET NEW.Grade = 'PASS';
 END IF;
 END`;
 con.query(sql);
 console.log("Before Insert query executed successfully..!");
 sql = "INSERT INTO STUDENT VALUES ('Aman', 22, 86, NULL)";
 con.query(sql);
 console.log("Record inserted successfully...!");
 console.log("Table records: ")
 sql = "SELECT * FROM STUDENT";
 con.query(sql, function(err, result){
 if (err) throw err;
 console.log(result);
 });
});

Output

The output produced is as follows −

Before Insert query executed successfully..!
Record inserted successfully...!
Table records:
[
  { Name: 'Jane', Age: 20, Score: 24, Grade: 'FAIL' },
  { Name: 'John', Age: 21, Score: 76, Grade: 'PASS' },
  { Name: 'John', Age: 21, Score: 76, Grade: 'PASS' },
  { Name: 'Aman', Age: 22, Score: 86, Grade: 'PASS' },
  { Name: 'Aman', Age: 22, Score: 86, Grade: 'PASS' }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class BeforeInsertTrigger {
   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...!");
            //lets create trigger on student table
            String sql = "Create Trigger sample_trigger BEFORE INSERT ON STUDENT FOR EACH ROW BEGIN IF NEW.Score < 35 THEN SET NEW.Grade = 'FAIL';
            ELSE SET NEW.Grade = 'PASS'; 
            END IF;
            END";
            st.execute(sql);
            System.out.println("Triggerd Created successfully...!");
            //lets insert some records into student table
            String sql1 = "INSERT INTO STUDENT VALUES ('John', 21, 76, NULL), ('Jane', 20, 24, NULL), ('Rob', 21, 57, NULL), ('Albert', 19, 87, NULL)";
            st.execute(sql1);
            //let print table records
            String sql2 = "SELECT * FROM STUDENT";
            rs = st.executeQuery(sql2);
            while(rs.next()) {
               String name = rs.getString("name");
               String age = rs.getString("age");
               String score = rs.getString("score");
               String grade = rs.getString("grade");
               System.out.println("Name: " + name + ", Age: " + age + ", Score: " + score + ", Grade: " + grade);
            }
      }catch(Exception e) {
         e.printStackTrace();
      }
   }
}

Output

The output obtained is as shown below −

Triggerd Created successfully...!
Name: John, Age: 21, Score: 76, Grade: PASS
Name: Jane, Age: 20, Score: 24, Grade: FAIL
Name: Rob, Age: 21, Score: 57, Grade: PASS
Name: Albert, Age: 19, Score: 87, Grade: PASS   

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()
trigger_name = 'sample_trigger'
table_name = 'Student' 
beforeInsert_trigger_query = f'''CREATE TRIGGER {trigger_name}
BEFORE INSERT ON {table_name}
FOR EACH ROW
BEGIN
IF NEW.Score < 35
THEN SET NEW.Grade = 'FAIL';
ELSE SET NEW.Grade = 'PASS';
END IF;
END'''
cursorObj.execute(beforeInsert_trigger_query)
print(f"BEFORE INSERT Trigger '{trigger_name}' is created successfully.")
# commit the changes and close the cursor and connection
connection.commit()
cursorObj.close()
connection.close()

Output

Following is the output of the above code −

BEFORE INSERT Trigger 'sample_trigger' is created successfully.
Advertisements