【15.0】MySQL进阶知识之触发器

【一】触发器介绍

【1】什么是触发器

在满足对表数据进行增删改的情况下，自动触发的功能，称为触发器

• 触发器是数据库管理系统中的一个概念，它是一种在数据库中定义的特殊对象，用于在满足特定条件时触发自动化的操作或逻辑。
  • 触发器主要用于监视数据库表的增删改操作，并在满足指定条件时触发相关的响应行为。
• 触发器通常与数据库表关联，当数据库表发生特定的数据变化时，触发器会自动执行相关的操作
  • 比如插入、更新、删除或查询数据等。
• 触发器可以作为一种数据库的约束，用于保证数据的完整性和一致性。

【2】触发器的特点

• 触发器通常与表一起创建、修改和删除。
• 触发器可以在特定的数据操作之前或之后触发执行。
• 触发器可以根据用户定义的条件判断是否执行相应的逻辑。
• 触发器可以调用存储过程、函数、触发其他触发器等，实现更复杂的业务逻辑。

【3】为何使用触发器

可以帮助我们实现日志、监控、处理等操作

• 使用触发器可以实现很多功能
  • 比如数据验证、数据补全、数据同步、日志记录等。
• 但需要注意，触发器的使用也需要谨慎，过多或不当的触发器可能会对数据库性能产生负面影响，因此在设计和使用触发器时应考虑到业务需求和性能方面的平衡。

【二】触发器的六种使用情况

• 增前
• 增后
• 删前
• 删后
• 改前
• 改后

【三】语法结构

create trigger 触发器的名字 
before/after insert/update/delete 
on 表名 for each row 
begin
	SQL语句
end

• 针对触发器的名字，我们要见名知意

【四】自定义触发器

【1】创建触发器

• 使用数据库管理系统提供的语法，创建一个新的触发器对象。
• 在创建触发器时，您需要指定触发器的名称、触发时机（例如在插入、更新或删除之前或之后）、触发的表以及触发时执行的逻辑。

【2】定义触发器逻辑

• 在创建触发器时，您需要定义触发器在触发时所执行的逻辑。
  • 这可以是任何数据库支持的操作
• 比如插入数据、更新数据、删除数据、查询数据等。
  • 您可以使用SQL语句或调用存储过程、函数来实现触发器的逻辑。

【3】定触发条件

• 根据您的需求，您可以为触发器指定触发条件。
  • 触发条件是一个逻辑表达式，当表中的数据满足该表达式时，触发器才会被激活执行相关的逻辑。
  • 例如，您可以指定只有当某列的值大于特定值时才触发触发器。

【4】绑定触发器

• 将触发器绑定到相应的表上。一般情况下，触发器会与指定的表相关联，当该表发生特定的数据操作时，触发器才会被触发执行。

【5】测试触发器

• 在绑定触发器后，您可以进行一些测试操作，验证触发器的逻辑是否按照预期执行。
  • 可以插入、更新或删除数据，观察触发器是否正确地处理这些操作。

【五】示例

【1】在插入之前触发示例

create trigger tri_before_insert_t1 before insert on t1 
for each row
begin
	sql语句
end

【2】在插入之后触发示例

create trigger tri_before_insert_t1 after insert on t1 
for each row
begin
	sql语句
end

【3】创建触发器示例（在插入数据前触发）

CREATE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
    -- 触发器逻辑
END;

【4】定义触发器逻辑示例（在插入数据前触发）

CREATE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
    -- 您可以在这里执行各种操作，比如插入另一张表、更新其他表、计算值等。
    -- 下面是一个示例，将新插入的数据中的某一列设置为当前时间。
    SET NEW.column_name = NOW();
END;

【5】绑定触发器示例

-- 将名为 "trigger_name" 的触发器绑定到名为 "table_name" 的表上
ALTER TABLE table_name
ADD CONSTRAINT trigger_name
AFTER INSERT ON table_name
FOR EACH ROW
BEGIN
    -- 触发器逻辑
END;

【6】查看当前库下所有的触发器信息

show triggers\G;

【7】删除当前库下指定的触发器信息

drop trigger 触发器名称;

【使用案例】

（1）创建表

CREATE TABLE cmd (
  id INT PRIMARY KEY auto_increment,
  USER CHAR (32),
  priv CHAR (10),
  cmd CHAR (64),
  sub_time datetime, #提交时间
  success enum ('yes', 'no') #0代表执行失败
);

CREATE TABLE errlog (
  id INT PRIMARY KEY auto_increment,
  err_cmd CHAR (64),
  err_time datetime
);

（2）需求

• cmd表插入数据的success如果值为no 则去errlog表中插入一条记录

delimiter $$  # 将mysql默认的结束符由;换成$$
create trigger tri_after_insert_cmd after insert on cmd for each row
begin
  if NEW.success = 'no' then  # 新记录都会被MySQL封装成NEW对象
      insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
  end if;
end $$
delimiter ;  # 结束之后记得再改回来，不然后面结束符就都是$$了

（3）仅仅往cmd表中插入数据

INSERT INTO cmd (
    USER,
    priv,
    cmd,
    sub_time,
    success
)VALUES
  ('kevin','0755','ls -l /etc',NOW(),'yes'),
  ('kevin','0755','cat /etc/passwd',NOW(),'no'),
  ('kevin','0755','useradd xxx',NOW(),'no'),
  ('kevin','0755','ps aux',NOW(),'yes');

（4）查看数据

select * from cmd;

select * from errlog;

【六】语句结束符

• 在写触发器的语法时，会遇到特殊情况，需要修改默认的语句结束符
• 临时修改的原因是因为触发器 存储过程等技术点 代码中也需要使用分号
• 如果不修改 则无法书写出完成的代码

【1】创建触发器

• 创建语句前修改默认语句结束符

delimiter $$

• 主要是为了区分错误执行语句和全局结束语句

create trigger tri_after_insert_cmd after insert on cmd 
for each row
begin
	if NEW.success = "no" then
		insert into errlog(err_cmd,err_time)
	values(NEW.cmd,NEW.sub_time);
		end if;
end $$

• 使用完以后要修改回原来的默认语句

delimiter ;

【2】删除触发器

drop tri_after_insert_cmd;

【七】参考模板

【1】在表中插入新记录时，自动生成唯一标识符

CREATE TRIGGER generate_uuid_trigger BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
    SET NEW.uuid = UUID();
END;

【2】在表中更新记录时，更新最后修改时间

CREATE TRIGGER update_last_modified_trigger BEFORE UPDATE ON table_name
FOR EACH ROW
BEGIN
    SET NEW.last_modified = NOW();
END;

【3】在表中删除记录时，将记录添加到历史记录表

CREATE TRIGGER archive_deleted_record_trigger AFTER DELETE ON table_name
FOR EACH ROW
BEGIN
    INSERT INTO history_table (id, deleted_at)
    VALUES (OLD.id, NOW());
END;

【4】在表中插入新记录时，检查是否满足某个条件，若不满足则取消插入操作

CREATE TRIGGER check_condition_trigger BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
    IF NEW.column_name < 10 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Value must be greater than 10';
    END IF;
END;

上述模板中的"table_name"和"column_name"应替换为您实际使用的表名和列名。

【5】日志模板

# 创建语句前修改默认语句结束符
delimiter $$
# 主要是为了区分错误执行语句和全局结束语句
create trigger tri_after_insert_cmd after insert on cmd 
for each row
begin
	if NEW.success = "no" then
		insert into errlog(err_cmd,err_time)
	values(NEW.cmd,NEW.sub_time);
		end if;
end $$
# 使用完以后要修改回原来的默认语句
delimiter ;