创建职工表以及职工工资表并创建触发器

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

建库 

mysql> CREATE DATABASE Work_self;

创建职工表

mysql> CREATE TABLE worker(
    -> WorkerID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    -> Workername VARCHAR(20),
    -> Workersex ENUM('m','f'),
    -> age INT
    -> );

创建工资表

mysql> CREATE TABLE wages(
    -> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    -> WorkerID INT NOT NULL,
    -> salary DOUBLE(10,2)
    -> );

 创建触发器  :对职工进行添加时 工资表中也要体现当前职工的信息

CREATE TRIGGER worker_insert_trigger
	AFTER INSERT ON worker
	FOR EACH ROW
	BEGIN
		INSERT INTO wages VALUES (NULL,new.WorkerID,10000);
	END;

测试：

INSERT INTO worker VALUES(1,'张一','f',20);
INSERT INTO worker VALUES(2,'张二','f',22);
INSERT INTO worker VALUES(3,'张三','f',24);

职工表 

 

 工资表

创建触发器：对职工进行修改时 工资表中也要一并修改当前职工的信息

CREATE TRIGGER worker_update_trigger
	AFTER UPDATE ON worker
	FOR EACH ROW
	BEGIN
		UPDATE wages SET WorkerID = new.WorkerID WHERE WorkerID = old.WorkerID;
	END;

修改并测试

UPDATE worker SET WorkerID = '66' WHERE WorkerID ='1';

 

 

 

 创建触发器：对职工进行解聘时 工资表中也要一并删除当前员工的工资信息

CREATE TRIGGER worker_delete_trigger
	AFTER DELETE ON worker
	FOR EACH ROW
	BEGIN
		DELETE FROM wages WHERE WorkerID = old.WorkerID;
	END;

删除数据查询

	DELETE FROM wages WHERE WorkerID = '2';
	DELETE FROM wages WHERE WorkerID = '3';