-- before insert 触发器（数据插入前校验）
delimiter //
create trigger check_user_email
before insert on users
for each row
begin
if new.email not like '%@%.%' then
signal sqlstate '45000'
set message_text = 'invalid email format';
end if;
end //
delimiter ;
-- after update 触发器（记录修改日志）
delimiter //
create trigger log_user_update
after update on users
for each row
begin
insert into user_audit_log
set user_id = old.id,
action = 'update',
changed_fields = concat(
if(old.username != new.username, 'username,', ''),
if(old.email != new.email, 'email,', '')
),
change_time = now();
end //
delimiter ;
-- after delete 触发器（数据归档）
delimiter //
create trigger archive_deleted_user
after delete on users
for each row
begin
insert into deleted_users_archive
select *, now() as delete_time
from users
where id = old.id;
end //
delimiter ;
