MySQL笔记: 触发器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
-- 事件触发的操作
-- 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 ;
文章目录