MySql數據庫觸發器使用教程

Posted on by

一、介紹

1、觸發器是一種特殊的存儲過程。觸發器和存儲過程一樣，是一個能夠完成特定功能、存儲在數據庫服務器上的SQL片段，但是觸發器無語調用，當對數據庫表中的數據執行DML操作時自動觸發這個SQL片段的執行，無需手動調用。

2、在MySql中，隻有執行insert，delete，update操作時才能觸發觸發器的執行

3、觸發器的這種特性可以協助應用在數據庫端確保數據的完整性，日志記錄，數據校驗等操作

4、使用別名OLD和NEW來引用觸發器中發生變化的記錄內容，這與其他的數據庫是相似的，現在觸發器隻支持行級觸發，不支持語句級觸發

二、操作

1、表數據準備

# 用戶表
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users`  (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用戶名',
  `sex` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '性別',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;


# 用戶操作日志表
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user_log
-- ----------------------------
DROP TABLE IF EXISTS `user_log`;
CREATE TABLE `user_log`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `create_time` datetime(0) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

2、觸發器格式

# 創建隻有一個執行語句的觸發器

create trigger 觸發器名 before|after 觸發事件
on 表名 for each row
執行語句；

# 創建有多個執行語句的觸發器

create trigger 觸發器名 before|after 觸發事件
on 表名 for each row
begin  
   執行語句列表
end;

3、操作

drop TRIGGER if EXISTS TRIGGER_test;
-- 需求1：當users表添加一行數據，則會自動在user_log添加日志記錄
delimiter $$
CREATE TRIGGER TRIGGER_test after INSERT
on users FOR EACH ROW
BEGIN
INSERT INTO user_log(content,create_time) VALUES('添加瞭一條數據',NOW());
end $$
delimiter ;

INSERT INTO users(user_name,sex) VALUES('xiaohemaio','男');


-- 需求2：當users表修改一行數據，則會自動在user_log添加日志記錄
drop TRIGGER if EXISTS TRIGGER_test1;

delimiter $$
CREATE TRIGGER TRIGGER_test1  BEFORE UPDATE
on users FOR EACH ROW
BEGIN
INSERT INTO user_log(content,create_time) VALUES('修改瞭一條數據',NOW());
end $$
delimiter ;

update users set user_name='迪麗熱巴' WHERE id=3;

三、觸發器NEW和OLD的使用

MySql中定義瞭NEW和OLD,用來表示觸發器的所在表中，觸發瞭觸發器的那一行數據，來引用觸發器中發生變化的記錄內容。

使用方法：NEW.columnName (columnName為相應數據表某一列名)

1、案例

-- 案例一
drop TRIGGER if EXISTS TRIGGER_test2;

delimiter $$
CREATE TRIGGER TRIGGER_test2 after INSERT
on users FOR EACH ROW
BEGIN
INSERT INTO user_log(content,create_time) VALUES(CONCAT('添加的用戶信息為：',NEW.user_name,' 性別為：',NEW.sex ),NOW());
end $$
delimiter ;

INSERT INTO users(user_name,sex) VALUES('xiaohemaio','男');


-- 案例二 
drop TRIGGER if EXISTS TRIGGER_test3;

delimiter $$
CREATE TRIGGER TRIGGER_test3  BEFORE UPDATE
on users FOR EACH ROW
BEGIN
INSERT INTO user_log(content,create_time) VALUES(CONCAT('將：',OLD.user_name,' 修改為：',NEW.user_name ),NOW());
end $$
delimiter ;

update users set user_name='迪麗熱巴' WHERE id=4;

-- 案例三
drop TRIGGER if EXISTS TRIGGER_test4;

delimiter $$
CREATE TRIGGER TRIGGER_test4  BEFORE DELETE
on users FOR EACH ROW
BEGIN
INSERT INTO user_log(content,create_time) VALUES(CONCAT('將id為：',OLD.user_name,' 已刪除' ),NOW());
end $$
delimiter ;

DELETE FROM  users WHERE id=4;

四、其他操作

-- 查看觸發器
SHOW TRIGGERS；

-- 刪除觸發器
drop TRIGGER if EXISTS 觸發器名;

五、註意事項

1、觸發器中不能對本表進行insert，update，delete操作，以免遞歸循環觸發

2、盡量少使用 觸發器，假設觸發器觸發每次執行1s，insert table 500條數據，那麼就需要觸發500次觸發器，光是觸發器執行的時間就花費瞭500s，而insert 500條數據一共是1s，那麼這個insert 的效率就非常低瞭。

3、觸發器是針對每一行的數據，對增刪改非常頻繁的表上切記不要使用觸發器，因為非常消耗資源。

補充：驗證觸發器

向用戶表users插入數據。

mysql> INSERT INTO users (userName, password, name, nickName, sex, email, isManager) VALUE ('itbilu', 'e10adc3949ba59abbe56e057f20f883e', 'IT筆錄', 'itbilu', 1, '[email protected]', 0);

users原來沒有數據，剛插入的數據userId為1。向用戶表角色表userRoles插入數據，使觸發器觸發：

mysql> INSERT INTO userRoles (userId, roleId) VALUE (1, 1);

剛才插入的數據已經使觸發器觸發，查看結果如下：

mysql> select isManager from users;
+-----------+
| isManager |
+-----------+
|         1 |
+-----------+

總結

到此這篇關於MySql數據庫觸發器使用的文章就介紹到這瞭,更多相關MySql觸發器使用內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：