数据库练习三

目标：

创建职工表以及职工工资表
职工表字段：工号，姓名，性别，年龄
工资表字段：编号自增，职工工号，基础工资10000，通过触发器实现：
对职工进行添加时 工资表中也要体现当前职工的信息                                                                    对职工进行修改时 工资表中也要一并修改当前职工的信息
对职工进行解聘时工资表中也要一并删除当前员工的工资信息

1.创建表

1.1创建职工表

CREATE TABLE Workers (
WorkerID INT PRIMARY KEY AUTO_INCREMENT COMMENT '工号',
Name VARCHAR(255) COMMENT '姓名',
Sex ENUM('W','M') COMMENT '性别',
Age INT COMMENT '年龄'
);

1.2创建职工工资表

CREATE TABLE Wages (
ID INT PRIMARY KEY AUTO_INCREMENT COMMENT '编号',
WorkerID INT NOT NULL COMMENT '职工工号',
Salary DOUBLE(10,2) COMMENT '工资',
FOREIGN KEY(WorkerID) REFERENCES Workers(WorkerID)
);

2.实现触发器

DELIMITER $$
CREATE TRIGGER WorkersInsertRecord
AFTER INSERT ON Workers
FOR EACH ROW
BEGIN
INSERT INTO Wages VALUES (NULL,new.WorkerID,10000);
END
$$
DELIMITER ;

3.添加

DELIMITER $$
CREATE TRIGGER WorkerUpdateRecord
AFTER UPDATE ON Workers
FOR EACH ROW
BEGIN
UPDATE Wages SET WorkerID = new.WorkerID WHERE WorkerID = old.WorkerID;
END
$$
DELIMITER ;

更新：UPDATE Workers SET WorkerID = '10' WHERE WorkerID ='3';

4.修改

DELIMITER $$
CREATE TRIGGER WorkerupdateRecord
AFTER UPDATE ON worker
FOR EACH ROW
BEGIN
UPDATE Wages SET WorkerID = new.WorkerID WHERE WorkerID = old.WorkerID;
END;
$$
DELIMITER ;

更新：UPDATE Workers SET WorkerID = '10' WHERE WorkerID ='3';

5.解聘（删除）

DELIMITER $$
CREATE TRIGGER WorkersDeleteRecord
AFTER DELETE ON Workers
FOR EACH ROW
BEGIN
DELETE FROM Wages WHERE WorkerID = old.WorkerID;
END
$$
DELIMITER ;

更新：DELETE FROM Workers WHERE WorkerID = '2';