创建职工表以及职工工资表
职工表字段:工号,姓名,性别,年龄
工资表字段:编号自增,职工工号,基础工资10000
通过触发器实现:
对职工进行添加时 工资表中也要体现当前职工的信息
对职工进行修改时 工资表中也要一并修改当前职工的信息
对职工进行解聘时 工资表中也要一并删除当前员工的工资信息
1、建库建表
create database test1;
#创建职工表
CREATE TABLE worker(
id INT PRIMARY KEY auto_increment,
name varchar(10),
sex ENUM('男','女'),
age int
)ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;
#创建职工工资表
CREATE TABLE salary(
sid int primary key auto_increment,
id int,
salary double(10,2)
)ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;
2、创建触发器,并插入一些数据后观察两个表的变化
#对职工进行添加时 工资表中也要体现当前职工的信息
CREATE TRIGGER tr_insert
AFTER INSERT ON worker
FOR EACH ROW
BEGIN
INSERT INTO salary VALUES(NULL,new.id,1000);
END;
INSERT INTO worker VALUES(1,'张三','男',20);
INSERT INTO worker VALUES(2,'王五','女',21);
INSERT INTO worker VALUES(3,'张四','男',22);
INSERT INTO worker VALUES(4,'张五','女',18);
INSERT INTO worker VALUES(5,'张六','男',18);
INSERT INTO worker VALUES(6,'张七','女',20);
#对职工进行修改时 工资表中也要一并修改当前职工的信息
CREATE TRIGGER tri_update
AFTER UPDATE ON worker
FOR EACH ROW
BEGIN
update salary SET id=new.id WHERE id=old.id;
END;
UPDATE worker SET id = 22 WHERE id=5;
UPDATE worker SET id = 33 WHERE id=6;
#对职工进行解聘时 工资表中也要一并删除当前员工的工资信息
CREATE TRIGGER tri_delete
AFTER DELETE ON worker
FOR EACH ROW
BEGIN
DELETE FROM salary WHERE id=old.id;
END;
DELETE FROM worker WHERE id=1;
DELETE FROM worker WHERE id=2;
DELETE FROM worker WHERE id=3;
以上就完成触发器的创建的,我们可以来观察一下其变化