---
title: MySQL笔记： 触发器
date: 2017-04-06 21:40:00
updated: 2017-04-06 21:40:00
description: "触发器"
categories: [个人笔记]
tags: [MySQL, MySQL笔记]
---

```sql
-- 事件触发的操作
-- insert update delete 语句创建触发操作

-- 
-- create trigger 触发器名称 before|after 触发事件(insert update delete)
-- on 表名字 for each row 
-- 执行的语句

-- 新建表用来记录添加数据事件的时间
CREATE TABLE trigger_time
(insert_time TIME);

CREATE TRIGGER insert_student_trigger BEFORE INSERT
ON student FOR EACH ROW
INSERT INTO trigger_time VALUES(NOW());

SELECT * FROM student;
INSERT INTO student VALUES(2014001,'敲不死','12300000',1);

SELECT * FROM trigger_time;


-- 关于触发器
-- 1. 基于基表
-- 2. before after
-- 3. for each row 行级触发器
-- 4. 触发器类select不能产生结果集
-- 5. 关键字old new
--    old代表就数据 New新数据
--    old.字段  new.字段

-- 作用:
-- 1. 插入检查

-- 需求: 对course插入的up_limit进行限制 60(小教室) 150(大教室) 230(阶梯教室);

DROP TRIGGER course_insert_before_trigger;

DELIMITER $$
CREATE TRIGGER course_insert_before_trigger BEFORE INSERT
ON course FOR EACH ROW
BEGIN
    IF(new.up_limit = 60 || new.up_limit = 150 || new.up_limit = 230) THEN
        SET new.up_limit = new.up_limit;
    ELSE
        -- 执行一条错误语句,报错，原先语句也就停止执行了
         INSERT INTO xxx VALUES(0);
    END IF;
END;
$$
DELIMITER ;


-- 先插入一条老师
INSERT INTO teacher VALUES('006','田老师','000000');
INSERT INTO course VALUES(7,'低等数学',60,'暂无','已审核','006');
choose


-- 2. 维护冗余数据

-- 添加剩余可选报人数字段
ALTER TABLE course ADD available INT;

-- 利用之前定义的视图更新available字段
UPDATE course 
SET available = up_limit - 
(SELECT student_num FROM available_course_view WHERE available_course_view.course_no = course.course_no);

-- 选报科目 插入数据到choose   更新course里面的available -1
DROP TRIGGER choose_insert_before_trigger;
DELIMITER $$
CREATE TRIGGER choose_insert_before_trigger BEFORE INSERT
ON choose FOR EACH ROW
BEGIN
    UPDATE course SET available = available - 1 WHERE course_no = new.course_no;
END
$$
DELIMITER ;

-- 147
INSERT INTO choose VALUES(NULL,2013020,2,NULL,NOW());

-- 作业
-- 取消选报 从choose删除数据   更新course里面的available +1

-- 3 替代级联删除

-- 部门表
CREATE TABLE dept(
    d_no INT PRIMARY KEY,
    d_name VARCHAR(30)
    );
    
-- 员工表
CREATE TABLE employee
(    
    e_no INT PRIMARY KEY,
    e_name VARCHAR(30),
    d_no INT,
    CONSTRAINT dept_member_fk FOREIGN KEY(d_no) REFERENCES dept(d_no)
);

INSERT INTO dept VALUES(1001,'销售部');
INSERT INTO dept VALUES(1002,'生产部');
INSERT INTO dept VALUES(1003,'开发部');

INSERT INTO employee VALUES(100001,'张三',1001);
INSERT INTO employee VALUES(100002,'李四',1002);
INSERT INTO employee VALUES(100003,'王五',1003);
INSERT INTO employee VALUES(100004,'赵六',1003);

DELETE FROM dept WHERE d_no = 1001;

SELECT * FROM dept;

DELIMITER $$
CREATE TRIGGER dept_delete_before_trigger BEFORE DELETE 
ON dept FOR EACH ROW
BEGIN
    DELETE FROM employee WHERE d_no = old.d_no;
END
$$
DELIMITER ;
```