mysql触发器语法

在开发中，我们经常会遇到有两个或多个相互关联的表，如商品信息和库存信息分别存放在两个不同的数据表中。

添加一条商品记录就要同时添加一条库存记录。这种可以使用触发器，让添加商品信息时自动插入一条库存信息。

这样可以保证数据的完整性，不用担心因为忘记添加库存数据而导致数据缺失。

MySQL从5.0.2开始支持触发器。它和存储过程一样，都是嵌入MySQL服务器的一段程序。

触发器是由事件触发某个操作，这些事件包括insert、update、delete事件。如果定义了某个触发程序，当数据库执行这些语句，就会触发那段程序。

当对数据表中的数据进行操作时，需要自动执行一些数据库逻辑时，可以使用触发器来实现。

创建触发器，语法：

create trigger 触发器名称
[before|after] [insert|update|delete] on 表名
for each row
触发器执行的语句块;

说明：

表名：表示触发器监控的对象。

before|after：表示触发的时间。before表示在事件之前触发；after表示在事件之后触发。

insert|update|delete：表示触发的事件。

insert表示插入记录时触发。

update表示更新记录时触发。

delete表示删除记录时触发。

触发器执行的语句块：可以是单条SQL语句，也可以是由begin...end结构组成的复合语句块。

创建before触发器，举栗子：

创建表

创建触发器

#创建触发器,插入test_trigger时往test_trigger_log插入记录delimiter //create trigger before_insert_test_tribefore insert on  test_triggerfor each row begin    insert into test_trigger_log(t_log) values('开始 插入');end //delimiter ;

验证触发器，插入数据时，自动触发触发器，往日志表中插入数据。

创建after触发器，举栗子：

创建触发器

delimiter //#删除test_trigger的数据，往日志表中插入create trigger after_delete_test_triafter delete on test_triggerfor each rowbegin   insert into test_trigger_log(t_log) values('开始删除');end //delimiter ;

验证删除数据后，日志表中自动添加了一条删除日志的记录。

处理业务逻辑，举栗子：

创建触发器，添加员工要检查新员工的薪资是否大于他领导的薪资。如果大于领导薪资，则报sqlstate_value为'HY000'的错误，从而添加失败。

delimiter //create trigger add_emp_tribefore insert on employeesfor each rowbegin  #声明变量，领导的工资  declare mgr_sal double;  #获取领导的工资  select salary into mgr_sal from employees  where employee_id =NEW.manager_id;  #判断  if NEW.salary >mgr_sal     then signal sqlstate 'HY000'      set message_text='薪资高于领导薪资错误';  end if;end //delimiter ;

验证，查询员工表的表结构

找一个当领导

添加一个新员工，工资8000，小于领导的工资可以添加成功。

添加一个新员工，工资20000，大于领导的工资添加失败。

查看触发器是查看数据库中已经存在的触发器的定义、状态和语法信息等。

方式1：查看当前数据库的所有触发器定义

show triggers;

方式2：查看当前数据库中某个触发器的定义

show create trigger 触发器名

方式3：从系统库information_schema的triggers表中查询某个触发器的信息。

select * from information_schema.triggers;

查看触发器，举栗子：

查看当前数据库的所有触发器定义

查看当前数据库中某个触发器的定义

从系统库information_schema的triggers表中查询某个触发器的信息。

使用drop删除，可以加if exists 判断存在就删除，避免报错。

触发器的优点：

保证数据的完整性

记录操作日志

操作数据前，对数据进行合法性的校验。

触发器的缺点：

触发器可读性差，隐蔽性强。看上去只是执行了一个update的操作，其实还执行了触发器里面的程序。

相关数据的变更，可能会导致触发器出错。触发器中使用的表结构变更，会导致触发器报错，影响到数据操作的正常运行。

注意点，因为外键导致表数据变更，触发器是不会触发的。