MySQL的触发器

  • 触发器,就是一种特殊的存储过程。触发器和存储过程一样是一个能够完成特定功能、存储在数据库服务器上的SQL片段,但是,触发器无需调用,当数据库表中的执行DML操作时自动触发这个SQL片段的执行,无需手动调用。
  • 在MySQL,只有执行INSERT,DELETE,UPDATE操作时才能触发触发器的执行。
  • 触发器的这种特性可以协助应用在数据库端确保数据的完整性,日志记录,数据校验等操作。
  • 使用别名OLD和NEW来应用触发器中发生变化的记录内容,这与其他的数据库是相似的。现在触发器还只支持行级触发,不支持语句级触发。

触发器的特性:

  1. 什么条件会触发:I、D、U
  2. 什么时候触发:在增删改之前或之后
  3. 触发频率:针对每一行执行
  4. 触发器定义在表上,附着在表上

基本操作

创建触发器

1.创建只有一个执行语句的触发器
CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW
执行语句;
2.创建有多个执行语句的触发器
CREATE TRIGGER 触发器名 BEFORE||AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
执行语句列表
END;

例如:

-- 数据准备
CREATE DATABASE mydb10_trigger;
USE mydb10_trigger;

-- 用户表
CREATE TABLE user(
uid INT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
password VARCHAR(50) NOT NULL
);

-- 用户信息操作日志表
CREATE TABLE user_logs(
id INT PRIMARY KEY AUTO_INCREMENT,
time TIMESTAMP,
log_text VARCHAR(255)
);

-- 需求一:当user表添加一行数据,则会自动在user_log添加日志记录
-- 定义触发器:trigger_test1
CREATE TRIGGER trigger_test1 AFTER INSERT
ON user FOR EACH ROW
INSERT INTO user_logs VALUES (NULL,NOW(),'有新用户添加');

-- 在user表添加数据,让触发器自动执行
INSERT INTO user VALUES(1,'张三','123456');
INSERT INTO user VALUES(2,'李四','234567'),(3,'王五','345678');

-- 需求二:当user表数据被修改,则会自动在user_log添加日志记录
DELIMITER $$
CREATE TRIGGER trigger_test2 BEFORE UPDATE
ON user FOR EACH ROW
BEGIN
INSERT INTO user_logs VALUES (NULL,NOW(),'有用户信息被修改');
END $$
DELIMITER ;

-- 在user表修改数据,让触发器自动执行
UPDATE user SET password = '888888' WHERE uid = 1;
UPDATE user SET password = '666666' WHERE uid = 1;

NEW和OLD

概念:MySQL中定义了NEW和OLD,用来表示触发器的所在表中,触发了触发器的那一行数据,来引用触发器中发生变化的记录内容,具体的:

触发器类型触发器类型NEW和OLD的使用
INSERT型触发器NEW表示将要或者已经新增的数据
UPDATE型触发器OLD表示修改之前的数据,NEW表示将要或已经修改后的数据
DELETE型触发器OLD表示将要或者已经删除的数据

方法:NEW.columnName (columnName为相应数据表某一列名)

-- 数据准备
CREATE DATABASE mydb10_trigger;
USE mydb10_trigger;

-- 用户表
CREATE TABLE user(
uid INT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
password VARCHAR(50) NOT NULL
);

-- 用户信息操作日志表
CREATE TABLE user_logs(
id INT PRIMARY KEY AUTO_INCREMENT,
time TIMESTAMP,
log_text VARCHAR(255)
);

-- 需求一:当user表添加一行数据,则会自动在user_log添加日志记录
-- 定义触发器:trigger_test1
CREATE TRIGGER trigger_test1 AFTER INSERT
ON user FOR EACH ROW
INSERT INTO user_logs VALUES (NULL,NOW(),'有新用户添加');

-- 在user表添加数据,让触发器自动执行
INSERT INTO user VALUES(1,'张三','123456');
INSERT INTO user VALUES(2,'李四','234567'),(3,'王五','345678');

-- 需求二:当user表数据被修改,则会自动在user_log添加日志记录
DELIMITER $$
CREATE TRIGGER trigger_test2 BEFORE UPDATE
ON user FOR EACH ROW
BEGIN
INSERT INTO user_logs VALUES (NULL,NOW(),'有用户信息被修改');
END $$
DELIMITER ;

-- 在user表修改数据,让触发器自动执行
UPDATE user SET password = '888888' WHERE uid = 1;
UPDATE user SET password = '666666' WHERE uid = 1;


-- INSERT类型的触发器
-- NEW
-- 定义触发器:trigger_test3
CREATE TRIGGER trigger_test3 AFTER INSERT
ON user FOR EACH ROW
INSERT INTO user_logs VALUES (NULL,NOW(),CONCAT('有新用户添加,信息为:',NEW.uid,NEW.username,NEW.password));
INSERT INTO user VALUES(4,'赵六','234567');

-- UPDATE类型的触发器

-- OLD
CREATE TRIGGER trigger_test4 AFTER UPDATE
ON user FOR EACH ROW
INSERT INTO user_logs VALUES(NULL,NOW(),CONCAT('有用户信息修改,信息修改之前为:',OLD.uid,OLD.username,OLD.password));

UPDATE user SET password ='9999999' WHERE uid=4;

-- NEW
CREATE TRIGGER trigger_test5 AFTER UPDATE
ON user FOR EACH ROW
INSERT INTO user_logs VALUES(NULL,NOW(),CONCAT_WS(',','有用户信息修改,信息修改之后为:',NEW.uid,NEW.username,NEW.password));

UPDATE user SET password ='000000' WHERE uid=1;

-- DELETE类型的触发器

-- OLD
CREATE TRIGGER trigger_test6 AFTER DELETE
ON user FOR EACH ROW
INSERT INTO user_logs VALUES(NULL,NOW(),CONCAT_WS(',','有用户信息被删除,被删除用户信息为:',OLD.uid,OLD.username,OLD.password));

DELETE FROM user WHERE uid=4;

其他操作

查看触发器

方法:
SHOW triggers;

删除触发器

方法:
DROP TRIGGER [IF EXISTS] trigger_name;

例如:

-- 查看触发器
SHOW TRIGGERS;

-- 删除触发器
DROP TRIGGER IF EXISTS trigger_test6;

注意事项

  1. MySQL中触发器不能对本表进行insert,update,delete操作,以免递归循环触发。
  2. 尽量少使用触发器,假设触发器触发每次执行1s,insert table 500条数据,那么就需要触发500触发器,光是触发器执行的时间就花费了500s,而insert 500条数据一共是1s,那么insert的效率就非常低了。
  3. 触发器是针对每一行的;对增删改非常频繁的表上切忌不要使用触发器,因为他会非常消耗资源。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

火眼猊

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值