MySQL - CREATE TRIGGER 创建触发器
触发器通常被定义为对事件的响应。例如，当我们将鼠标指针悬停在网站的下拉菜单上时，会显示一系列用于浏览该网站的选项。在这里，鼠标指针的悬停是一个事件，而下拉菜单中选项的显示则是触发器执行的结果。MySQL 中也引入了这一概念。
MySQL 中的触发器是类似于过程的存储程序。这些触发器可以在与事件关联的表、模式、视图和数据库上创建，并且每当事件发生时都会调用相应的触发器。
实际上，触发器是为了响应以下任何事件而执行的:
- 数据库操作 (DML) 语句(DELETE、INSERT 或 UPDATE)
- 数据库定义 (DDL) 语句(CREATE、ALTER 或 DROP)。
- 数据库操作(SERVERERROR、LOGON、LOGOFF、STARTUP 或 SHUTDOWN)。
在 MySQL 中创建触发器
您可以使用 CREATE TRIGGER 语句创建触发器。
语法
以下是 MySQL CREATE 语句的语法TRIGGER 语句。
CREATE TRIGGER trigger_name trigger_time trigger_event ON table_name FOR EACH ROW BEGIN ... END;
其中:
- trigger_name 是您需要创建的触发器的名称
- trigger_time 是触发器激活的时间
- trigger_event 可以是 INSERT、UPDATE 或 DELETE。此事件会导致触发器被调用。
- table_name 是与触发器关联的表的名称。
示例
假设我们创建了一个名为 student 的表，如下所示 -
CREATE TABLE STUDENT( Name varchar(35), Age INT, Score INT );
以下查询将创建一个触发器，如果您输入的分数小于 0，则会将分数设置为 0。
DELIMITER // CREATE TRIGGER sample_trigger BEFORE INSERT ON STUDENT FOR EACH ROW BEGIN IF NEW.Score < 0 THEN SET NEW.Score = 0; END IF; END // DELIMITER ;
验证
如果您尝试在学生表中插入记录，并且使用小于 0 的年龄值，则年龄将自动设置为 0。
INSERT INTO STUDENT VALUES
('Jeevan', 22, 8),
('Raghav', 26, -3),
('Pooja', 21, -9),
('Devi', 30, 9);
创建的STUDENT表将包含以下记录 -
| Name | Age | Score |
|---|---|---|
| Jeevan | 22 | 8 |
| Raghav | 26 | 0 |
| Pooja | 21 | 0 |
| Devi | 30 | 9 |
如我们所见，表中没有插入负值，因为它们都被替换为零。
使用客户端程序创建触发器
我们也可以使用客户端程序创建触发器。
语法
要通过 PHP 程序创建触发器，我们需要使用 mysqli 函数 query() 执行 CREATE 语句，如下所示 -
$sql = "CREATE TRIGGER testTrigger AFTER UPDATE ON Student FOR EACH ROW INSERT INTO Student SET action = 'update', Name = OLD.Name, age = OLD.age, score = OLD.score"; $mysqli->query($sql);
要通过 JavaScript 程序创建触发器，我们需要使用 mysql2 库的 query() 函数执行 CREATE 语句，如下所示 -
sql = "CREATE TRIGGER testTrigger AFTER UPDATE ON Student FOR EACH ROW INSERT INTO Student SET action = 'update', Name = OLD.Name, age = OLD.age, score = OLD.score" con.query(sql);
要通过 Java 程序创建触发器，我们需要使用 JDBC 函数 execute() 执行 CREATE 语句，如下所示 -
String sql = "CREATE TRIGGER testTrigger AFTER UPDATE ON Student FOR EACH ROW INSERT INTO Student SET action = 'update', Name = OLD.Name, age = OLD.age, score = OLD.score"; statement.execute(sql);
要通过 Python 程序创建触发器，我们需要使用 MySQL Connector/Python 的 execute() 函数执行 CREATE 语句，如下所示 -
create_trigger_query = 'CREATE TRIGGER sample_trigger
BEFORE INSERT ON students
FOR EACH ROW
BEGIN
IF NEW.Score < 0 THEN
SET NEW.Score = 0;
END IF;
END'
cursorObj.execute(create_trigger_query)
示例
以下是程序 -
$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 testTrigger AFTER UPDATE ON Student FOR EACH ROW INSERT INTO Student SET action = 'update', Name = OLD.Name, age = OLD.age, score = OLD.score";
if($mysqli->query($sql)){
printf("Trigger created successfully...!");
}
if($mysqli->error){
printf("Failed..!" , $mysqli->error);
}
$mysqli->close();
输出
获得的输出如下 -
Trigger created successfully...!
var mysql = require('mysql2');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});
//连接到 MySQL
con.connect(function(err) {
if (err) throw err;
//console.log("Connected successfully...!");
//console.log("--------------------------");
sql = "USE TUTORIALS";
con.query(sql);
sql = "CREATE TRIGGER testTrigger AFTER UPDATE ON Student FOR EACH ROW INSERT INTO Student SET action = 'update', Name = OLD.Name, age = OLD.age, score = OLD.score"
con.query(sql);
console.log("Trigger created successfully....!");
sql = "SHOW TRIGGERS";
con.query(sql, function(err, result){
if (err) throw err;
console.log(result);
});
});
输出
生成的输出如下 -
Trigger created successfully....!
[
{
Trigger: 'testTrigger',
Event: 'UPDATE',
Table: 'student',
Statement: "INSERT INTO Student SET action = 'update', Name = OLD.Name, age = OLD.age, score = OLD.score",
Timing: 'AFTER',
Created: 2023-08-01T05:21:18.540Z,
sql_mode: 'IGNORE_SPACE,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION',
Definer: 'root@localhost',
character_set_client: 'utf8mb4',
collation_connection: 'utf8mb4_unicode_ci',
'Database Collation': 'utf8mb4_0900_ai_ci'
}
]
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class CreateTrigger {
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 = "CREATE TRIGGER testTrigger AFTER UPDATE ON Student FOR EACH ROW INSERT INTO Student SET action = 'update', Name = OLD.Name, age = OLD.age, score = OLD.score";
st.execute(sql);
System.out.print("Trigger created successfully....!");
}catch(Exception e) {
e.printStackTrace();
}
}
}
输出
获得的输出如下所示 -
Trigger created successfully....!
import mysql.connector
#建立连接
connection = mysql.connector.connect(
host='localhost',
user='root',
password='password',
database='tut'
)
table_name = 'Student'
trigger_name = 'sample_trigger'
# 创建游标对象
cursorObj = connection.cursor()
# Create table
create_table_query = '''
CREATE TABLE Student(
Name VARCHAR(35),
age INT,
Score INT
)
'''
cursorObj.execute(create_table_query)
print("The table is created successfully!")
# Creating a trigger
create_trigger_query = f'''CREATE TRIGGER {trigger_name}
BEFORE INSERT ON {table_name}
FOR EACH ROW
BEGIN
IF NEW.Score < 0 THEN
SET NEW.Score = 0;
END IF;
END'''
cursorObj.execute(create_trigger_query)
print(f"Trigger '{trigger_name}' is created successfully.")
# Commit the changes and close the cursor and connection
connection.commit()
cursorObj.close()
connection.close()
输出
以下是上述代码的输出 -
The table is created successfully! Trigger 'sample_trigger' is created successfully.
