mysql,一起初学触发器

标题：mysql中的触发器

案例一：
CREATE TABLE t_persons(
	id INT PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(10),
	age INT ,
	sex CHAR(1)

);

CREATE TABLE t_courses(
	id INT PRIMARY KEY AUTO_INCREMENT,
	coursesName VARCHAR(10),
	personsId INT

);

ALTER TABLE t_courses ADD COLUMN score INT;


1.在表t_persons上创建INSERT触发器，当学生插入后就给t_courses表插入
一条记录（给这个新入学的学生分配课程）
DELIMITER $
CREATE TRIGGER g_afterInsert_tPersons
AFTER INSERT
ON t_persons FOR EACH ROW
BEGIN
	INSERT INTO t_courses(coursesName,personsId,score) VALUES('入学课程',new.id,100);
END $

INSERT INTO t_persons(username,age,sex) VALUES('张三',18,'男');

SELECT * FROM t_persons;
SELECT * FROM t_courses;

2.在表t_persons 上创建UPDATE触发器，当学生数据更新后，判断学生的姓名是否是特别关注，
如果是，那就把这个学生的成绩更新为0；

DELIMITER $
CREATE TRIGGER g_afterUpdate_tPersons
AFTER UPDATE
ON t_persons FOR EACH ROW
BEGIN
	IF new.username='张三' THEN
		UPDATE t_courses SET score=90 WHERE new.id=t_courses.`id`;
	END IF;
END $

UPDATE t_persons SET sex='女' WHERE id=1; 

3.在表t_persons 上创建DELETE 触发器，当学生记录被删除后，就删除课程表中对应的学生课程记录
DELIMITER $
CREATE TRIGGER g_afterDelete_TPersons
AFTER DELETE
ON t_persons FOR EACH ROW
BEGIN
	DELETE FROM t_courses WHERE t_courses.`id`=old.id;
END $

DELETE FROM t_persons WHERE id=1;

4.在表t_persons 上创建INSERT触发，在学生记录插入之前检查数据是否符合规定

DELIMITER $
CREATE TRIGGER g_beforeInsert_tPersons
BEFORE INSERT
ON t_persons FOR EACH ROW
BEGIN
	IF(new.age>1000) THEN
		  SET new.age=500;
	END IF;
END $

DROP TRIGGER g_beforeInsert_tPersons;

INSERT INTO t_persons(username,age,sex) VALUES('ajdkfa',10000,'女');

SELECT * FROM t_persons;


案例二：
创建数据库schools
CREATE DATABASE schools;
创建表users
CREATE TABLE users(
	id INT PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(10),
	count_fan INT,
	count_follow INT
);
创建表logic
CREATE TABLE logic(
	id INT PRIMARY KEY AUTO_INCREMENT,
	fan_uid INT,
	up_uid INT
);

CREATE TABLE dynamics(
	id INT PRIMARY KEY AUTO_INCREMENT,
	uid INT,
	var VARCHAR(20),
	c_time DATETIME
);

创建触发器
DELIMITER $
CREATE TRIGGER g_afterInsert_logic
AFTER INSERT
ON logic FOR EACH ROW
BEGIN 
	UPDATE users SET count_fan=count_fan+1 WHERE new.up_uid=users.`id`;
	UPDATE users SET count_follow=count_follow+1 WHERE new.fan_uid=users.`id`;
	INSERT INTO dynamics(uid,var) VALUES(new.fan_uid,'关注了');
END $
使用该语句时，触发了上述触发器
INSERT INTO logic(fan_uid,up_uid) VALUES(2,1);

SELECT * FROM dynamics;
SELECT * FROM users;
SELECT * FROM dynamics;

INSERT INTO users(username,count_fan,count_follow) 
VALUES('up',0,0),
('fan',0,0);