SQL触发器--刘世民

CREATE DATABASE IF NOT EXISTS  test DEFAULT CHARSET utf8;
USE test;
#创建学生表
CREATE TABLE IF NOT EXISTS student
(
    username VARCHAR(50),
    PASSWORD VARCHAR(60),
    stuid INT PRIMARY KEY AUTO_INCREMENT,
    birthday DATE
    
);
#插入数据
INSERT INTO student(username,PASSWORD,birthday)
    VALUES('王二','1111111','2016-08-23');
    
#查询
SELECT * FROM student;
    
#成绩表
CREATE TABLE IF NOT EXISTS cj
(
    num INT PRIMARY KEY AUTO_INCREMENT,
    stu_id INT,
    stu_name VARCHAR(50),
    math FLOAT,
    chinese FLOAT,
    english FLOAT
    
);
SELECT * FROM cj;
#触发器1:插入
DELIMITER $
CREATE TRIGGER abc
AFTER INSERT ON student FOR EACH ROW
BEGIN
INSERT INTO cj(stu_id,stu_name)VALUES(new.stuid,new.username);
END $
DELIMITER ;

INSERT INTO student(username,PASSWORD,birthday)
    VALUES('张三','222222','2016-08-23');
    
    
#触发器2： 删除
DELIMITER $
CREATE TRIGGER bcd
AFTER INSERT ON student FOR EACH ROW
BEGIN
DELETE FROM cj.stu_id=old.stuid;
END $
DELIMITER ;

#触发器3：修改语句
DELIMITER $
CREATE TRIGGER def
ALTER INSERT ON student FOR EACH ROW
BEGIN
UPDATE  cj SET cj.stu_name=old.username WHERE cj.stu_id=old.stuid;
END $
DELIMITER ;