触发器和事件自动化

一、触发器

1、触发器的基本概念

触发器是和表相关的一种数据库对象，可以将他看作一种特殊的存储过程，不需要人为调动的存储过程。

关键字：trigger
基本作用：通过对表进行数据的插入、更新或删除等操作来触发，从而执行某些特定的操作。

2、触发器的创建

可以在MySql命令界面通过’? create trigger '命令查看创建触发器的格式。

mysql> ? create trigger

Description:
Syntax:
CREATE
[DEFINER = user] – 用户名
TRIGGER trigger_name – 触发器名
trigger_time --触发时机
 trigger_event – 触发条件
 ON tbl_name --表名
FOR EACH ROW – 对每一行
trigger_body – 触发器所做的事情

创建触发器的基本格式：

create TRIGGER <trigger_name> <trigger_time> <trigger_event> ON tbl_name FOR EACH ROW <trigger_body>

3.【NEW和OLD的使用】：

触发器的使用

使用触发器实现检查约束

create database db_3;
create table test_tb(
id int primary key auto_increment,
test_name varchar(20) not null,
test_age int)auto_increment = 100;
# 创建触发器实现，规定年龄的取值范围必须在0~100之间。
delimiter //
create trigger tri_test
before insert on test_tb
for each row
begin
  if test_age >100 then set new.test_age = 100;
  else test_age <0 then set new.test_age = 0;
  end if;
end//
delimiter ;

列2

/**** 1、创建和使用触发器 ****/
// 创建触发器（AFTER INSERT表示触发器的动作：插入一条数据后要干的事，NEW和OLD分别表示将要新增或者修改删除的数据）
CREATE TRIGGER trig_book AFTER INSERT 
    ON t_book FOR EACH ROW 
  UPDATE t_bookType SET bookNum=bookNum+1 WHERE NEW.bookTypeId=t_bookType.id;
// 创建具有多个执行语句的触发器
DELIMITER || # DELIMITER ||  #将语句的分隔符改为||
CREATE TRIGGER trig_book AFTER DELETE
    ON t_book FOR EACH ROW
    BEGIN
  UPDATE t_bookType SET bookNum=bookNum-1 WHERE OLD.bookTypeId=t_bookType.id;
  INSERT INTO t_log VALUES(1,NOW(),"在book表中删除了一条数据");
    END ||
DELIMITER ; # 将语句的分隔符改回原来的分号";"
/**** 2、查看和删除触发器 ****/
SHOW TRIGGERS;
DROP TRIGGER trig_book;

查看数据库中的触发器

mysql 触发器都是保存在information_shema数据库中的，所以查询触发器需要查询information_shema数据库中的triggers表。

格式： use information_shema;

select * from  triggers;  # 查看所有触发器。
select *from triggers where trigger='触发器名'; #条件查询触发器。
show triggers from <数据库>; # 查看某个数据库中的触发器。

删除触发器

格式：drop trigger <触发器名>；

触发器的使用限制

触发器中不能使用存储过程和函数，也不能使用select和call等动态的sql语句，改变表结构。
触发器中不能使用开始和结束事务。
触发器不能写的太复杂，否则每改变一行，所执行的任务就太多了。

突破触发器的使用限制

虽然触发器中不能使用select查询数据，不能直接通过参数返回，但是可以通过用户变量带回数据。

create trigger tri_test
after insert on <dbname>  for each row
begin
select new.<属性> from <dbname> 
where new.<属性> = valare into @变量;
end;
select @变量;

select "错误" into @information;  -- 没有结果集的返回
 select @information;
 select 'aaa','bbbb' into @a,@b;
 select @a;

触发器的注意事项

触发器中只要有一条语句出现错误，那么整个触发器以及触发此触发器的sql语句不会执行。

触发器的使用示例

-- 使用触发器制作日志
-- 日志：对某些操作/事件的记录。
/* 1.创建日志表*/
create table review(
id int primary key auto_increment,
    user_name varchar(20),-- 用户名
    database_name varchar(20), -- 数据库名
    table_name varchar(30), -- 数据表名
    action_time datetime, -- 操作时间。
    action_name varchar(10),-- 操作名称。
)
-- 2.创建触发器实现日志的记录
create trigger tri_test_insert
after insert
on test_tb
for each row 
insert into review(user_name,database_name, table_name,action_time,action_name) 
values(user(),database(),"test_tb",now(),"insert");

触发器总结

1、触发器的限制

触发程序不能调用将数据返回客户端的存储程序，也不能使用采用CALL语句的动态SQL语句，但是允许存储程序通过参数将数据返回触发程序，也就是存储过程或者函数通过OUT或者INOUT类型的参数将数据返回触发器是可以的，但是不能调用直接返回数据的过程。
不能在触发器中使用以显示或隐式方式开始或结束事务的语句，如START TRANS-ACTION,COMMIT或ROLLBACK。

2、触发器的作用

（1）安全性—可以基于数据库的值使用户具有操作数据库的某种权利

可以基于时间限制用户的操作，例如不允许下班后和节假日修改数据库数据。
可以基于数据库中的数据限制用户的操作，例如不允许股票的价格的升幅一次超过10%。

（2）审计—可以跟踪用户对数据库的操作

审计用户操作数据库的语句。
把用户对数据库的更新写入审计表。

（3）实现复杂的数据完整性规则

实现非标准的数据完整性检查和约束。触发器可产生比规则更为复杂的限制。与规则不同，触发器可以引用列或数据库对象。例如，触发器可回退任何企图吃进超过自己保证金的期货。
提供可变的缺省值。

（4）自动计算数据值，如果数据的值达到了一定的要求，则进行特定的处理

3、触发器总结

触发器是基于行触发的，所以删除、新增或者修改操作可能都会激活触发器，所以不要编写过于复杂的触发器，也不要增加过多的触发器，这样会对数据的插入、修改或者删除带来比较严重的影响，同时也会带来可移植性差的后果，所以在设计触发器的时候一定要有所考虑。

触发器是一种特殊的存储过程，它在插入，删除或修改特定表中的数据时触发执行，它比数据库本身标准的功能有更精细和更复杂的数据控制能力。

二、事件概述

在MySQL 5.1中新增了一个特色功能事件调度器（Event Scheduler），简称事件。

事件（event）是MySQL在相应的时刻调用的过程式数据库对象。一个事件可调用一次，也可周期性的启动，它由一个特定的线程来管理的，也就是所谓的“事件调度器”。

事件和触发器类似，都是在某些事情发生的时候启动。当数据库上启动一条语句的时候，触发器就启动了，而事件是根据调度事件来启动的。由于他们彼此相似，所以事件也称为临时性触发器。事件取代了原先只能由操作系统的计划任务来执行的工作，而且MySQL的事件调度器可以精确到每秒钟执行一个任务，而操作系统的计划任务（如：Linux下的CRON或Windows下的任务计划）只能精确到每分钟执行一次。

事件的优缺点

1、优点

一些对数据定时性操作不再依赖外部程序，而直接使用数据库本身提供的功能。可以实现每秒钟执行一个任务，这在一些对实时性要求较高的环境下就非常实用了。

2、缺点

定时触发，不可以调用。

事件调度器参数

事件由一个特定的线程来管理。启用事件调度器后，拥有SUPER权限的账户执行SHOW PROCESSLIST就可以看到这个线程了。

一句话 与触发器类似，都是在特定条件下执行相应的操作，但触发器是在触发时才执行，而事件是让数据库定时实现某操作。

事件的创建 event

格式：

CREATE EVENT [IF NOT EXISTS] <event_name> ON SCHEDULE <schedule > DO <event_body>;

一条create event语句创建一个事件。每个事件由两个主要部分组成，第一部分是事件调度（eventschedule，表示事件何时启动以及按什么频率启动;
第二部分是事件动作（event action），这是事件启动时执行的代码，事件的动作包含一条SQL语句，它可能是一个简单地insert或者update语句，也可以使一个存储过程或者
benin…end语句块，这两种情况允许我们执行多条SQL。
一个事件可以是活动（打开）的或停止（关闭）的，活动意味着事件调度器检查事件动作是否必须调用，停止意味着事件的声明存储在目录中，但调度器不会检查它是否应该调用。在一个事件创建之后，它立即变为活动的，一个活动的事件可以执行一次或者多次。

说明：

一些常用的时间间隔设置

--每隔5秒钟执行
ON SCHEDULE EVERY 5 SECOND
--每隔1分钟执行
ON SCHEDULE EVERY 1 MINUTE
--每天凌晨1点执行
ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR)
--每个月的第一天凌晨1点执行
ON SCHEDULE EVERY 1 MONTH STARTS DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY),INTERVAL 1 MONTH),INTERVAL 1 HOUR)
--每 3 个月，从现在起一周后开始
ON SCHEDULE EVERY 3 MONTH STARTS CURRENT_TIMESTAMP + 1 WEEK
--每十二个小时，从现在起三十分钟后开始，并于现在起四个星期后结束
ON SCHEDULE EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 30 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 4 WEEK

事件（定时任务）管理

1、查询事件

在MySQL中可以通过查询information_schema.events表，查看已创建的事件。

--查看当前所在库的事件
show events;
--查看所有事件
SELECT * FROM information_schema.events;

2、修改事件

事件被创建之后，还可以使用ALTER EVENT语句修改其定义和相关属性

ALTER
  [DEFINER={user | CURRENT_USER}]
  EVENT [IF NOT EXISTS] event_name
  ON SCHEDULE schedule
  [ON COMPLETION [NOT] PRESERVE]
  [ENABLE | DISABLE | DISABLE ON SLAVE]
  [COMMENT 'comment']
  DO event_body;

4、删除事件

删除已经创建的事件可以使用DROP EVENT语句来实现。

DROP EVENT IF EXISTS event_name;

实例演示

1、事件+sql

创建名称为event_t1的事件，用于每隔5秒钟向数据表t1(用户信息表）中插入一条数据。

-- 创建用户信息表
CREATE TABLE IF NOT EXISTS t1
(
 id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户编号',
 name VARCHAR(30) NOT NULL COMMENT '用户姓名',
 create_time TIMESTAMP COMMENT '创建时间'
) COMMENT = '用户信息表';
-- 创建事件
CREATE EVENT IF NOT EXISTS event_t1
ON SCHEDULE EVERY 5 SECOND
ON COMPLETION PRESERVE
COMMENT '新增用户信息定时任务'
DO INSERT INTO t1(name,create_time) VALUES('hwb',NOW());

2、事件+存储过程

--创建总表
CREATE TABLE IF NOT EXISTS t_total
(
 userNumber INT  COMMENT '用户数',
 createtime TIMESTAMP COMMENT '创建时间'
) COMMENT = '总表';
--创建名称为t_total的存储过程，用于统计前面表插入的数量
CREATE PROCEDURE t_total()
BEGIN
 DECLARE n_total INT default 0;
 SELECT COUNT(*) INTO n_total FROM t1;
 INSERT INTO t_total (userNumber,createtime) VALUES(n_total,NOW());
END;
--创建名称为event_total的事件，用于在每天12点调用存储过程。
CREATE EVENT IF NOT EXISTS event_total
ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 12 HOUR)
ON COMPLETION PRESERVE ENABLE
DO CALL t_total();

3、每隔10s向表中插入一个数据

-- 每隔10s向表中插入一个数据
-- 创建测试表
create even_test_tb(
id int primary key auto_increment,
insert_time datetime);
create event event_test1
on schedule every 10 second
do
insert into even_test_tb(insert_time) values(now());

事件调度器 scheduler

所有事件都是由事件调度器管理（scheduler）的，所以打开事件调度器才能执行事件管理

查看事件调度器的状态：show variable 会话变量(variable) 查看会话变量。# 事件调度器是一个会话
查看事件调度器show variables like’%scheduler%';
关闭事件调度器：set global[会话变量] event_scheduler = off / 0
打开事件调度器：set global event_scheduler = 1 / on
事件调度器开始后是由一个进程来执行的，

查看进程列表：show processlist;

-- 创建一个清空表数据的事件，每个二分钟清空表event_test_tb
create event event_test_tb
on schedule every 2 minute
starts now()||current_timestamp()+interval 1 minute
ends current_timestamp+interval 5 minute-- 5分钟后结束
on completion not preserve -- 事件停止后不保留（删除）事件
do  truncate table event_test_tb; -- 清空表数据
|| delete from event_test_tb;

-- 创建一个清空表数据的事件，固定时间点清空表数据
create event event_test1
on schedule at"23:00:00"+interval 1 day-- 固定点执行事件
on completion not preserve -- 事件停止后不保留（删除）事件
do  truncate table event_test_tb; -- 清空表数据
|| delete from event_test_tb;

事件的状态

某些时候事件没有执行，这是就需要查询事件的执行状态，

show event --查询所有事件状态。
-- 关闭某个事件
alter event event_test1 disable;
-- 打开
alter event event_test enable;

本章笔记是在网上找的资料以及自己的理解总结出来的笔记希望可以帮助大家，感谢大家的耐心观看如有错误请即使联系我我会及时修正

触发器和事件自动化的讲解