mysql触发器

use test;
CREATE TABLE t_owners_log (
	updatetime date,
	ownerid INT,
	oldname VARCHAR (30),
	newname VARCHAR (30)
);
-- 触发器(trigger)：监视某种情况，并触发某种操作。
-- 
-- 触发器创建语法四要素：
-- 1.监视地点(table) 
-- 2.监视事件(insert/update/delete) 
-- 3.触发时间(after/before) 
-- 4.触发事件(insert/update/delete)
-- create trigger 触发器名字 触发时间 触发事件 on 表 for each row
-- begin
--     -- 触发器内容主体，每行用分号结尾
-- end

-- CREATE <触发器名> <BEFORE | AFTER >
-- <INSERT | UPDATE | DELETE>
-- ON <表名> FOR EACH Row<触发器主体>
use test;
DROP trigger IF EXISTS tri_account_updatenum1;

-- AFTER UPDATE：在更新记录之后触发。通常用于在数据更新后执行其他操作，如记录更改历史或发送通知。

-- 创建INSERT触发器  
-- BEFORE UPDATE
create trigger tri_account_updatenum1 
before update on test.t_owners for each row
begin 
	insert into t_owners_log(updatetime,ownerid,oldname,newname) values
	-- 或者insert into t_owners_log values
	(
		now(),
		old.id,
		old.name,
		new.name
	);
end

update test.t_owners set name="王汉生" where name="王刚";

# 触发器给新用户发送邮件

drop table if exists user_info; 
drop table if exists email_info;

create table user_info(
	id int not null auto_increment primary key,
	name varchar(30),
	email varchar(50)
);
insert into user_info(id, name, email) values(1, 'zhangsan', 'zhangsan@qq.com'); 
insert into user_info(id, name, email) values(2, 'lisi', 'lisi@qq.com');

create table email_info(
	id int not null auto_increment primary key, 
	email varchar(50),
	content text,
	send_time datetime
);
insert into email_info(id,email,content,send_time) values(1,"110@qq.com","发送邮箱成功",now())


-- BEFORE INSERT
create trigger checkEmail
before insert on user_info for each row 
begin 
	if not new.email REGEXP "@[a-z]+.com"  then 
		SIGNAL SQLSTATE '45000'
		SET MESSAGE_TEXT = "邮箱格式不正确";
	end if;
end

insert into user_info(id, name, email) values(5, 'ceshi', 'lisiqq.com');
-- 查看触发器
SHOW TRIGGERS;
-- 删除触发器
DROP TRIGGER validate_related_records; 

-- BEFORE INSERT
CREATE TRIGGER checkEmail
BEFORE INSERT ON user_info FOR EACH ROW 
BEGIN 
    DECLARE error_message VARCHAR(255);
    
    IF NOT new.email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,4}$' THEN
        SET error_message = "邮箱格式不正确";
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = error_message;
    END IF;
END;

-- AFTER INSERT
create trigger addUserinfo_records
after insert on customers for each row 
begin 
	Insert into customer_status(customer_id, status_notes) 
	VALUES(NEW.customer_id, '账户创建成功')
end

-- BEFORE DELETE:在删除数据前，检查是否有关联数据，如有，停止删除操作。
create trigger validate_related_records
before delete on user_info for each row
begin 
	IF OLD.id in (select id from email_info) THEN
		SIGNAL SQLSTATE '45000'
		SET MESSAGE_TEXT = '这位用户有相关联的信息，不能删除。';
	END if; 
end

insert into email_info(id,email,content,send_time) values(1,"110@qq.com","发送邮箱成功",now())

delete from user_info where id=1;
# SQL 错误 [1644] [45000]: 这位用户有相关联的信息，不能删除。

-- AFTER DELETE:删除表 A 信息后，自动删除表 B 中与表 A 相关联的信息
create trigger delete_related_info
after delete on user_info for each row
begin 
	delete from email_info where id = old.id;
end

delete from user_info where id=1;

SHOW TRIGGERS