MySQL - 创建触发器

Table of content
上一个
下一个

触发器通常定义为对事件的响应。例如，当我们将鼠标指针悬停在网站的下拉菜单上时，就会显示一组用于浏览该网站的选项。这里，鼠标指针的悬停是一个事件，而下拉菜单中选项的显示是触发器执行的结果。这个概念也引入了 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 ;

验证

如果您尝试在 student 表中插入记录，并且如果您使用小于 0 的值作为年龄，它将自动设置为 0。

INSERT INTO STUDENT VALUES
('Jeevan', 22, 8),
('Raghav', 26, -3),
('Pooja', 21, -9),
('Devi', 30, 9);

创建的 STUDENT 表将包含以下记录 -

姓名 年龄 分数
Jeevan 22 8
Raghav 26 0
Pooja 21 0
Devi 30 9

正如我们所看到的，表中没有插入负值，因为它们都被替换为零。

使用客户端程序创建触发器

我们还可以使用客户端程序创建触发器。

语法

PHP NodeJS Java Python

要通过 PHP 程序创建触发器，我们需要使用名为 query() 的 mysqli 函数执行 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)

示例

以下是程序 -

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 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"
});

//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 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://127.0.0.1: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
#Establishing the connection
connection = mysql.connector.connect(
   host='localhost',
   user='root',
   password='password',
   database='tut'
)
table_name = 'Student'
trigger_name = 'sample_trigger'
# Creating a cursor object
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.
打印页面
上一个
下一个
广告