存储过程实例，对应表的操作记录

最新推荐文章于 2022-08-20 14:36:53 发布

郭周园

最新推荐文章于 2022-08-20 14:36:53 发布

阅读量173

点赞数

文章标签： python mysql java 数据库大数据

本文链接：https://blog.csdn.net/qvanminpiao/article/details/111403581

版权

对应表下创建触发器：

begin
insert into staff_update_record (staff_id,old_info,new_info,created_at,type) values(new.id,'新添加','新添加',CURRENT_TIMESTAMP(),1);

end

BEGIN
call staff_update(new.id,new.department_id,old.department_id,new.center_id,old.center_id,new.store_id,old.store_id,new.status,old.status,new.positive,old.positive,new.employ_date,old.employ_date,new.is_on_circulation,old.is_on_circulation,new.is_seven_days,old.is_seven_days);
END

函数中写好自己的存储过程：

CREATE DEFINER=CURRENT_USER PROCEDURE `staff_update`(IN `staff_id` int,IN `new_department_id` int,IN `old_department_id` int,IN `new_center_id` int,IN `old_center_id` int,IN `new_store_id` int,IN `old_store_id` int,IN `new_status` int,IN `old_status` int,IN `new_positive` int,IN `old_positive` int,IN `new_employ_date` int,IN `old_employ_date` int,IN `new_is_on_circulation` int,IN `old_is_on_circulation` int,IN `new_is_seven_days` int,IN `old_is_seven_days` int)
BEGIN

declare old_department_write VARCHAR(32) default '无';
declare new_department_write VARCHAR(32) default '无';

declare old_center_write VARCHAR(32) default '无';
declare new_center_write VARCHAR(32) default '无';

declare old_store_write VARCHAR(32) default '无';
declare new_store_write VARCHAR(32) default '无';

declare new_status_write VARCHAR(32) default '无';
declare old_status_write VARCHAR(32) default '无';

declare new_positive_write VARCHAR(32) default '无';
declare old_positive_write VARCHAR(32) default '无';

declare new_employ_date_write VARCHAR(32) default '无';
declare old_employ_date_write VARCHAR(32) default '无';

declare new_is_on_circulation_write VARCHAR(32) default '无';
declare old_is_on_circulation_write VARCHAR(32) default '无';

declare new_is_seven_days_write VARCHAR(32) default '无';
declare old_is_seven_days_write VARCHAR(32) default '无';

IF new_department_id <> old_department_id OR new_center_id <> old_center_id OR new_store_id <> old_store_id THEN

       select CONCAT_WS('-',store.store_name,center.center_name,CONCAT(department.department_number,'部')) as old into old_department_write from department LEFT JOIN department_relation on department_relation.department_id = department.id LEFT JOIN center on department_relation.center_id = center.id LEFT JOIN store on department_relation.store_id = store.id where department.id = old_department_id;

       select CONCAT_WS('-',store.store_name,center.center_name,CONCAT(department.department_number,'部')) as new into new_department_write from department LEFT JOIN department_relation on department_relation.department_id = department.id LEFT JOIN center on department_relation.center_id = center.id LEFT JOIN store on department_relation.store_id = store.id where department.id = new_department_id;

       insert into staff_update_record (staff_id,old_info,new_info,created_at,type) values(staff_id,old_department_write,new_department_write,CURRENT_TIMESTAMP(),2);
END IF;

IF new_status <> old_status THEN

   IF new_status = 0 THEN
   set new_status_write = '在职';
   ELSEIF new_status = 1 THEN
       set new_status_write = '离职';
   ELSE
       set new_status_write = '停职';
   END IF;

   IF old_status = 0 THEN
   set old_status_write = '在职';
   ELSEIF old_status = 1 THEN
       set old_status_write = '离职';
   ELSE
       set old_status_write = '停职';
   END IF;

   insert into staff_update_record (staff_id,old_info,new_info,created_at,type) values(staff_id,old_status_write,new_status_write,CURRENT_TIMESTAMP(),4);
END IF;


IF new_positive <> old_positive THEN

   IF new_positive = 0 THEN
   set new_positive_write = '未转正';
   ELSE
       set new_positive_write = '转正';
   END IF;

   IF old_positive = 0 THEN
   set old_positive_write = '未转正';
   ELSE
       set old_positive_write = '转正';
   END IF;

   insert into staff_update_record (staff_id,old_info,new_info,created_at,type) values(staff_id,old_positive_write,new_positive_write,CURRENT_TIMESTAMP(),5);
END IF;

IF new_employ_date <> old_employ_date THEN

   set new_employ_date_write = FROM_UNIXTIME(new_employ_date,'%Y-%m-%d %H:%i:%S');
   set old_employ_date_write = FROM_UNIXTIME(old_employ_date,'%Y-%m-%d %H:%i:%S');

   insert into staff_update_record (staff_id,old_info,new_info,created_at,type) values(staff_id,old_employ_date_write,new_employ_date_write,CURRENT_TIMESTAMP(),6);
END IF;

IF new_is_on_circulation <> old_is_on_circulation THEN

   IF new_is_on_circulation = 0 THEN
   set new_is_on_circulation_write = '开启';
   ELSE
       set new_is_on_circulation_write = '关闭';
   END IF;

   IF old_is_on_circulation = 0 THEN
   set old_is_on_circulation_write = '开启';
   ELSE
       set old_is_on_circulation_write = '关闭';
   END IF;

   insert into staff_update_record (staff_id,old_info,new_info,created_at,type) values(staff_id,old_is_on_circulation_write,new_is_on_circulation_write,CURRENT_TIMESTAMP(),7);
END IF;

IF new_is_seven_days <> old_is_seven_days THEN

   IF new_is_seven_days = 0 THEN
   set new_is_seven_days_write = '否';
   ELSE
       set new_is_seven_days_write = '是';
   END IF;

   IF old_is_seven_days = 0 THEN
   set old_is_seven_days_write = '否';
   ELSE
       set old_is_seven_days_write = '是';
   END IF;

   insert into staff_update_record (staff_id,old_info,new_info,created_at,type) values(staff_id,old_is_seven_days_write,new_is_seven_days_write,CURRENT_TIMESTAMP(),8);
END IF;

END