MySQL触发器练习

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

建表

CREATE TABLE `worker` (     

`工号` INT(11)  AUTO_INCREMENT PRIMARY KEY,     

`姓名` VARCHAR(20) ,     

`性别` VARCHAR(4),     

`年龄` INT(4)    

 );





CREATE TABLE `wages`(

`编号` INT(11)  AUTO_INCREMENT PRIMARY KEY ,

`工号` VARCHAR(20) ,

`工资`   DECIMAL(10,2)  

);

触发器使用Navicat Premium 15工具建立

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

检验

INSERT INTO worker VALUES (1,"张三","男",18);

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

检验

UPDATE worker SET `工号` = 2 WHERE `工号` = 1;

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

检验

DELETE  FROM worker WHERE `工号` =2;