一文帶你瞭解MySQL中觸發器的操作

2023-02-20 06:01:22

概述

介紹

觸發器，就是一種特殊的儲存過程。觸發器和儲存過程一樣是一個能夠完成特定功能、儲存在資料庫伺服器上的SQL片段，但是觸發器無需呼叫，當對資料庫表中的資料執行DML操作時自動觸發這個SQL片段的執行，無需手動條用。
在MySQL中，只有執行insert,delete,update操作時才能觸發觸發器的執行
觸發器的這種特性可以協助應用在資料庫端確保資料的完整性，紀錄檔記錄，資料校驗等操作。
使用別名OLD和NEW來參照觸發器中發生變化的記錄內容，這與其他的資料庫是相似的。現在觸發器還只支援行級觸發，不支援語句級觸發。

觸發器的特性

什麼條件會觸發:l、D、U
什麼時候觸發:在增刪改前或者後
觸發頻率:針對每一行執行
觸發器定義在表上，附著在表上

操作—建立觸發器

格式

1.建立只有一個執行語句的觸發器

2.建立有多個執行語句的觸發器

操作

演示

create database if not exists test_trigger;
 
use test_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)
);
 
-- 需求1：當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');

結果展示

create database if not exists test_trigger;
 
use test_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)
);
 
-- 需求1：當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');
 
-- 需求2：當user表資料被修改時，則會自動在user_log新增紀錄檔記錄
delimiter $$
create trigger trigger_test2 after update on user 
for each row 
begin 
	insert into user_logs values(null,now(),'有使用者資訊被修改');
end $$
delimiter ;
 
update `user` set password ='222222' where uid=1;

需求2結果是

操作—new和old

格式

MySQL中定義了NEW和oLD，用來表示觸發器的所在表中，觸發了觸發器的那一行資料，來參照觸發器中發生變化的記錄內容，具體地:

觸發器型別	觸發器型別的new和old使用
insert型觸發器	new表示將要或已經新增的資料
update型觸發器	OLD表示修改之前的資料，NEW表示將要或已經修改後的資料
delete型觸發器	OLD表示將要或者已經刪除的資料

使用方法

new.columname(columname為相應資料表某一列名)

演示

create trigger trigger_test3 after insert on user
for each row 
begin 
	insert into user_logs  values(null,now(),concat('有新使用者新增，資訊為：',new.uid,new.username,new.password)) ;
end;
 
insert into `user` values(2,'李四','24234');

-- update 
create trigger trigger_test4 after update on user 
for each row 
begin 
	insert into user_logs values(null,now(),concat_ws('--','使用者資訊有修改，修改後為：',new.uid,new.username,new.password));
end;
 
update `user`  set password='66666' where uid=2;

-- update 
create trigger trigger_test5 after update on user 
for each row 
begin 
	insert into user_logs values(null,now(),concat_ws('--','使用者資訊有修改，修改後為：',new.uid,new.username,new.password,
															'使用者資訊有修改，修改前為：',old.uid,old.username,old.password));
end;
 
update `user`  set password='988765654' where uid=2;