-- 事件触发的操作
-- 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 ;
