1、什么是触发器

触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性。 举个例子，比如你现在有两个表【用户表】和【日志表】，当一个用户被创建的时候，就需要在日志表中插入创建的 log 日志，如果在不使用触发器的情况下，你需要编写程序语言逻辑才能实现，但是如果你定义了一个触发器，触发器的作用就是当你在用户表中插入一条数据的之后帮你在日志表中插入一条日志信息。当然触发器并不是只能进行插入操作，还能执行修改，删除。

2、创建触发器

CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW
trigger_stmt
trigger_name：触发器的名称
tirgger_time：触发时机，为 BEFORE 或者 AFTER
trigger_event：触发事件，为 INSERT、DELETE 或者 UPDATE
tb_name：表示建立触发器的表明，就是在哪张表上建立触发器
trigger_stmt：触发器的程序体，可以是一条 SQL 语句或者是用 BEGIN 和 END 包含的多条语
句
所以可以说 MySQL 创建以下六种触发器：
BEFORE INSERT,BEFORE DELETE,BEFORE UPDATE
AFTER INSERT,AFTER DELETE,AFTER UPDATE

创建多个语句构成的触发器

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
执行语句列表
END

代码范例：

MYSQL* mysql = new MYSQL();
    MYSQL* pDB = mysql_init(mysql);
    if (pDB == NULL) {
        std::cout << "mysql_init failed" << std::endl;
        return -1;
    }

    pDB = mysql_real_connect(pDB, "localhost", "root", "root", "mysql", 3306, NULL, 0);

    if (pDB) {

        std::string sql = "CREATE DATABASE hello"; // SQL语句可替换为自己实际需要执行的语句

        // 创建数据库
        int ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
        if (ret != 0) {
            std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
            return -1;
        }
        MYSQL_RES* res = mysql_use_result(mysql);
        if (res) {
            show_result(res);

            mysql_free_result(res);
        }


        // 授予权限
        sql = "GRANT ALL ON hello.* TO 'hello'@'localhost';"; // SQL语句可替换为自己实际需要执行的语句
        ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
        if (ret != 0) {
            std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
            return -1;
        }
        res = mysql_use_result(mysql);
        if (res) {
            show_result(res);

            mysql_free_result(res);
        }

        // 指定数据库
        sql = "USE hello";
        ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
        if (ret != 0) {
            std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
            return -1;
        }

        sql = "SET NAMES 'utf8';";
        ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
        if (ret != 0) {
            std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
            return -1;
        }

        sql = "SET CHARACTER SET utf8;";
        ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
        if (ret != 0) {
            std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
            return -1;
        }

        // 创建表
        sql = "CREATE TABLE IF NOT EXISTS `hello` (`编号` NVARCHAR(16) PRIMARY KEY,";
        sql += "`age` INT NOT NULL DEFAULT 18";
        sql += ")ENGINE = InnoDB DEFAULT CHARSET = utf8; ";
        ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
        if (ret != 0) {
            std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
            return -1;
        }

        sql = "CREATE TABLE IF NOT EXISTS `teacher` (`编号` NVARCHAR(16) PRIMARY KEY,";
        sql += "`age` INT NOT NULL DEFAULT 18";
        sql += ")ENGINE = InnoDB DEFAULT CHARSET = utf8; ";
        ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
        if (ret != 0) {
            std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
            return -1;
        }

        // 日志表
        sql = "CREATE TABLE `logs` (`Id` int(11) NOT NULL AUTO_INCREMENT, `log` varchar(255) DEFAULT NULL COMMENT \"日志说明\", PRIMARY KEY(`Id`) )ENGINE=InnoDB DEFAULT CHARSET = utf8mb4 COMMENT=\"日志\";";
        ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
        if (ret != 0) {
            std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
            return -1;
        }

        // 创建触发器
        sql = "CREATE TRIGGER hello_log AFTER INSERT ON `hello` FOR EACH ROW \n \
            BEGIN\n \
            DECLARE s1 VARCHAR(40)character set utf8;\n \
            DECLARE s2 VARCHAR(40)character set utf8;\n \
            SET s2 = \" is created\"; \n \
            SET s1 = CONCAT(NEW.`编号`, s2);\n \
            INSERT INTO logs(log) values(s1);\n \
            END;";
        // CONCAT 用于拼接数据，此处的作用是将新数据的`编号`与s2结合
        ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
        if (ret != 0) {
            std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
            return -1;
        }
        
        sql = "INSERT INTO `hello` (`编号`, `age`) VALUES(\"9527\", 99)";
        ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
        if (ret != 0) {
            std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
            return -1;
        }

        sql = "INSERT INTO `hello` (`编号`, `age`) VALUES(\"9528\", 23)";
        ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
        if (ret != 0) {
            std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
            return -1;
        }

        sql = "INSERT INTO `teacher` (`编号`, `age`) VALUES(\"9529\", 99)";
        ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
        if (ret != 0) {
            std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
            return -1;
        }


        sql = "INSERT INTO `teacher` (`编号`, `age`) VALUES(\"9530\", 100)";
        ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
        if (ret != 0) {
            std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
            return -1;
        }
        
        sql = "SELECT age FROM `hello` UNION SELECT age FROM `teacher`;";
        ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
        if (ret != 0) {
            std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
            return -1;
        }
        res = mysql_store_result(mysql);
        if (res) {
            show_result(res);
            mysql_free_result(res);
        }

        // 删除数据库
        sql = "DROP DATABASE hello";
        ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
        if (ret != 0) {
            std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
            return -1;
        }

        mysql_close(pDB);
        std::cout << pDB << std::endl;
        delete mysql;
    }

    mysql = nullptr;

    return 0;

执行结果：当hello表被插入数据时，触发器会被触发