触发器
注意几个问题：
1.begin…end
2.什么条件触发
Insert、Update、Delete
3.什么时候触发：Before、After（i、u、d）
4.触发频率：针对每一行数据触发一次。
5.定义在表上，附着在表上。
6.触发器的begin…end之前的语句的执行效率一定要高，这样资源消耗才小。
7.NEW和OLD：代表的是一行，
触发器的语法：
CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
[trigger_order]
trigger_body
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }
trigger_order: { FOLLOWS | PRECEDES } other_trigger_name例子1：在插入之前触发
CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
DELIMITER $$
CREATE TRIGGER ins_sum BEFORE INSERT ON account
FOR EACH ROW
begin
SET @sum = @sum + NEW.amount; --》触发器会被触发三次
end$$
DELIMITER ;
mysql> SET @sum = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT @sum AS 'Total amount inserted';
+-----------------------+
| Total amount inserted |
+-----------------------+
| 1852.48 |
+-----------------------+
1 row in set (0.00 sec)
mysql> drop trigger ins_sum;
Query OK, 0 rows affected (0.00 sec)例子2：在修改之前触发
delimiter $$
CREATE TRIGGER upd_check BEFORE UPDATE ON account
FOR EACH ROW
BEGIN
IF NEW.amount < 0 THEN --》某一行，更新后的叫new，更新前叫old
SET NEW.amount = 0;
ELSEIF NEW.amount > 100 THEN
SET NEW.amount = 100;
END IF;
END$$
delimiter ;
mysql> select * from account;
+----------+---------+
| acct_num | amount |
+----------+---------+
| 137 | 14.98 |
| 141 | 1937.50 |
| 97 | -100.00 |
+----------+---------+
3 rows in set (0.00 sec)
mysql> update account set amount=-10 where acct_num=137;
Query OK, 1 row affected (0.33 sec)
Rows matched: 1 Changed: 1 Warnings: 0 ---》表示执行了触发器
mysql> select * from account;
+----------+---------+
| acct_num | amount |
+----------+---------+
| 137 | 0.00 |
| 141 | 1937.50 |
| 97 | -100.00 |
+----------+---------+
3 rows in set (0.00 sec)
mysql> update account set amount=200 where acct_num=137;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from account;
+----------+---------+
| acct_num | amount |
+----------+---------+
| 137 | 100.00 |
| 141 | 1937.50 |
| 97 | -100.00 |
+----------+---------+
3 rows in set (0.01 sec)例子3：更新前把old数据备份到备份表里
create table account_bak as select * from account where 1=2;
delimiter $$
CREATE TRIGGER UPDATE_check BEFORE update ON account
FOR EACH ROW
BEGIN
insert into account_bak values(OLD.acct_num,OLD.amount);
END$$
delimiter ;
mysql> select * from account;
+----------+---------+
| acct_num | amount |
+----------+---------+
| 137 | 100.00 |
| 141 | 1937.50 |
| 97 | -100.00 |
+----------+---------+
3 rows in set (0.00 sec)
mysql> select * from account_bak; --》account_bak表刚开始是空的。
Empty set (0.00 sec)
mysql> update account set amount=200 where acct_num=141; --》执行update语句
Query OK, 1 row affected (0.34 sec)
Rows matched: 1 Changed: 1 Warnings: 0 --》证明调用了触发器
mysql> select * from account_bak;
+----------+---------+
| acct_num | amount |
+----------+---------+
| 141 | 1937.50 |
+----------+---------+
1 row in set (0.00 sec)event调度器
event概念：可以理解为预约计划系统做某些操作。
①一次②周期性执行
周期的开始、周期的结束、每隔多长时间（每分钟、每秒、每天、每个季度、每周…）
通常，指定周期的开始、间隔
event语法：
CREATE
[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;
schedule:（时刻）
AT timestamp [+ INTERVAL interval] ... --》从现在开始
| EVERY interval
[STARTS timestamp [+ INTERVAL interval] ...]
[ENDS timestamp [+ INTERVAL interval] ...]
interval:（间隔）
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}例子1：
mysql>create table t1(id int,mycol int);
mysql> insert into t1 values(1,1);
Query OK, 1 row affected (0.00 sec):
mysql> insert into t1 values(2,2);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t1 values(3,3);
Query OK, 1 row affected (0.01 sec) 打开数据库的event执行调度
mysql> show variables like '%event_scheduler%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | OFF |
+-----------------+-------+
1 row in set (0.01 sec)
mysql> set global event_scheduler =1;
Query OK, 0 rows affected (0.00 sec)重新登录再执行下面的语句：
mysql> show variables like '%event_scheduler%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | ON |
+-----------------+-------+
1 row in set (0.00 sec) --》证明event调度打开了。delimiter $$
CREATE EVENT myevent
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
DO
begin
UPDATE t1 SET mycol = mycol + 1;
end$$
delimiter ;
mysql>show events;
/var/log/mysqld.log
2016-01-17T10:40:52.018009Z 10 [Note] Event Scheduler: scheduler thread started with id 10create table t2(id int auto_increment primary key,t_time datetime);
delimiter $$
CREATE EVENT e_daily
ON SCHEDULE
EVERY 1 MINUTE
COMMENT 'Saves total number of sessions then clears the table each day'
DO
BEGIN
INSERT INTO t2 values (null,current_timestamp);
END $$
delimiter ;
mysql> select EVENT_NAME,LAST_EXECUTED from information_schema.EVENTS;
+------------+---------------------+
| EVENT_NAME | LAST_EXECUTED|
+------------+---------------------+
| e_daily |2016-01-17 18:53:09|
+------------+---------------------+
1 row in set (0.00 sec)mysql> use TENNIS;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show events G --》查看events的具体信息
*************************** 1. row ***************************
Db: TENNIS
Name: e_daily
Definer: root@localhost
Time zone: Asia/Shanghai
Type: RECURRING
Execute at: NULL
Interval value: 1
Interval field: MINUTE
Starts: 2016-01-17 18:52:09
Ends: NULL
Status: ENABLED
Originator: 0
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
mysql>