MySQL触发器配合游标实现数据同步处理

最新推荐文章于 2022-07-27 18:24:18 发布

营山第一型男

最新推荐文章于 2022-07-27 18:24:18 发布

阅读量161

点赞数

分类专栏：笔记

本文链接：https://blog.csdn.net/qq_38053711/article/details/119828542

版权

笔记专栏收录该内容

6 篇文章 0 订阅

订阅专栏

DELIMITER $$

USE `beifen`$$

DROP TRIGGER /*!50032 IF EXISTS */ `monitor_userdepartmentchange_test`$$

CREATE
    /*!50017 DEFINER = 'root'@'%' */
    TRIGGER `monitor_userdepartmentchange_test` AFTER UPDATE ON `tsm_user` 
    FOR EACH ROW BEGIN
	#用户新部门ID
	DECLARE di VARCHAR(64);
	#资产旧部门ID
	DECLARE ov VARCHAR(64);
	#资产ID
	DECLARE aid VARCHAR(64);
	#判断循环是否关闭的标志位
	DECLARE done TINYINT DEFAULT 0;
	DECLARE res CURSOR FOR SELECT id FROM tfam_asset WHERE userid = NEW.id;
	DECLARE EXIT HANDLER FOR SQLSTATE '02000' SET done=1;
	SET di = NEW.DEPTID;
	IF OLD.DEPTID<>di THEN
		OPEN res;
			WHILE done<>1 DO
				FETCH res INTO aid;
				#查询旧的部门ID值
				SET ov = (SELECT usedeptid FROM tfam_asset WHERE id = aid);
				#修改资产的归属部门
				UPDATE tfam_asset SET usedeptid = di WHERE id = aid;
				#插入调拨记录
				INSERT INTO tfam_alter(id,`year`,`month`,`date`,asset_id,alter_type,old_value,new_value,reason,creatorid,create_time) VALUES(UUID(),YEAR(CURDATE()),MONTH(CURDATE()),NOW(),aid,'0008',ov,di,'自动调拨','system',NOW());
			END WHILE;
		CLOSE res;
	END IF;
    END;
$$

DELIMITER ;