MySQL触发器使用

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

创建职工表

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

创建工资表

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)
);

添加新增信息时，工资表随职工表增添信息的触发器

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

插入数据，并查看两表变化，判断触发器是否成功

INSERT INTO Workers VALUES (1,"张三",'M',20);
INSERT INTO Workers VALUES (2,"李四",'M',32);
INSERT INTO Workers VALUES (3,"王舞",'W',23);

添加职工信息表信息更新时，工资表同时更新的触发器

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';

添加职工信息表信息删除信息时，工资表同时删除对应信息的触发器

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';