sql触发器编程

创建职工表以及职工工资表
职工表字段：工号，姓名，性别，年龄
工资表字段：编号自增，职工工号，基础工资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;

以上就完成触发器的创建的，我们可以来观察一下其变化