【一】触发器介绍
【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 ;