Your cart is currently empty!
Category: mysql
-
Khóa học miễn phí MySQL – Before Insert Trigger nhận dự án làm có lương
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
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 −
$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.
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
Khóa học miễn phí MySQL – After Update Trigger nhận dự án làm có lương
MySQL – After Update Trigger
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
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 −
$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:UsersLenovoAppDataLocalProgramsPythonPython310libsite-packagesmysqlconnectorconnection_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:UsersLenovoDesktopuntitled.py", line 29, in
cursorObj.execute(update_query)
File "C:UsersLenovoAppDataLocalProgramsPythonPython310libsite-packagesmysqlconnectorcursor_cext.py", line 330, in execute
result = self._cnx.cmd_query(
File "C:UsersLenovoAppDataLocalProgramsPythonPython310libsite-packagesmysqlconnectoropentelemetrycontext_propagation.py", line 77, in wrapper
return method(cnx, *args, **kwargs)
File "C:UsersLenovoAppDataLocalProgramsPythonPython310libsite-packagesmysqlconnectorconnection_cext.py", line 641, in cmd_query
raise get_mysql_exception(
mysql.connector.errors.DatabaseError: 1644 (45000): Age Cannot be Negative
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