Mysql表数据创建3种触发器更新数据

最新推荐文章于 2023-12-12 11:13:14 发布

码奴生来只知道前进~

最新推荐文章于 2023-12-12 11:13:14 发布

阅读量4.1k

点赞数

分类专栏： mysql 文章标签： mysql 数据库大数据

本文链接：https://blog.csdn.net/tanqingfu1/article/details/126540547

版权

mysql 专栏收录该内容

31 篇文章 2 订阅

订阅专栏

1、创建2张测试表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for data_analysis
-- ----------------------------
DROP TABLE IF EXISTS `data_analysis`;
CREATE TABLE `data_analysis`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `data_source` int(11) NULL DEFAULT NULL COMMENT '数据源 / 数源单位数 / 注册审核通过数 / 数据仓数',
  `data_table` int(11) NULL DEFAULT NULL COMMENT '数据表/ 已归集接口数 / 注册用户数 / 资源项',
  `meta_data` int(11) NULL DEFAULT NULL COMMENT '元数据 / 已归集字段数 / 组织个数 / 数据项',
  `application_total` int(11) NULL DEFAULT NULL COMMENT '应用系统数 / 接口调用总数 / 角色个数 / 数据量',
  `life_cycle` int(11) NULL DEFAULT NULL COMMENT '生命周期异常数 / 应用系统 / 用户活跃度',
  `table_abnormal` int(11) NULL DEFAULT NULL COMMENT '表监控异常数 / 资源项',
  `field_abnormal` int(11) NULL DEFAULT NULL COMMENT '字段监控异常数 / 数据项',
  `data_total` int(11) NULL DEFAULT NULL COMMENT '数据量',
  `type` int(11) NULL DEFAULT NULL COMMENT '类型(0-数据分析,1-数据共享,2-系统平台使用,3-数据仓建设)',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_bin COMMENT = '数据分析、数据共享、系统平台使用、数据仓建设' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of data_analysis
-- ----------------------------
INSERT INTO `data_analysis` VALUES (1, 1290, 2111278, 5110, 110, 110, 1150, 550, NULL, 0, '2022-08-24 15:07:17', '2022-08-24 15:07:37');
INSERT INTO `data_analysis` VALUES (2, 90, 1278, 55145, 1110, 110, 1952, 3254, 31224, 1, '2022-08-24 15:07:17', '2022-08-24 15:27:36');
INSERT INTO `data_analysis` VALUES (3, 2216, 11236, 257, 79, 90, NULL, NULL, NULL, 2, '2022-08-24 15:07:17', '2022-08-24 15:27:36');
INSERT INTO `data_analysis` VALUES (4, 6, 11252, 223752, 1231211, NULL, NULL, NULL, NULL, 3, '2022-08-24 15:07:17', '2022-08-26 11:39:48');

-- ----------------------------
-- Table structure for data_warehouse
-- ----------------------------
DROP TABLE IF EXISTS `data_warehouse`;
CREATE TABLE `data_warehouse`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '名称',
  `resources_total` int(11) NULL DEFAULT NULL COMMENT '资源项',
  `data_item` int(11) NULL DEFAULT NULL COMMENT '数据项',
  `data_total` int(11) NULL DEFAULT NULL COMMENT '数据量',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10 CHARACTER SET = utf8 COLLATE = utf8_bin COMMENT = '数据仓建设' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of data_warehouse
-- ----------------------------
INSERT INTO `data_warehouse` VALUES (1, '公益诉讼主题库', 12, 25, 25874, '2022-08-24 15:29:52', '2022-08-25 16:34:12');
INSERT INTO `data_warehouse` VALUES (2, '民生主题库', 25, 38, 2541, '2022-08-24 15:30:16', '2022-08-25 16:09:34');
INSERT INTO `data_warehouse` VALUES (3, '人社主题库', 35, 25, 54774, '2022-08-24 15:30:35', '2022-08-25 16:09:31');
INSERT INTO `data_warehouse` VALUES (4, '形式主题库', 15, 24, 254, '2022-08-24 15:31:03', '2022-08-25 16:09:27');
INSERT INTO `data_warehouse` VALUES (5, '晚年主体库', 25, 35, 25874, '2022-08-25 15:28:41', '2022-08-25 15:28:44');
INSERT INTO `data_warehouse` VALUES (6, '少年主题库', 25, 14, 258741, '2022-08-25 15:29:17', '2022-08-25 15:29:20');

2、创建触发器

2.1通过navicat操作界面创建

3个触发器的功能都是一样的，数据新增、更新、删除重新统计数据更新到另一张表。

3、通过sql创建触发器

DROP TRIGGER IF EXISTS `insert_data`;
delimiter ;;
CREATE TRIGGER `insert_data` AFTER INSERT ON `data_warehouse` FOR EACH ROW UPDATE `data_analysis` 
SET 
	-- 数据仓数
	`data_source` = (SELECT COUNT(*) FROM data_warehouse),
	-- 资源项
	`data_table` = (SELECT sum(resources_total) from data_warehouse),
	-- 数据项
	`meta_data` = (SELECT sum(data_item) from data_warehouse),
	-- 数据量
	`application_total` = (SELECT sum(data_total) from data_warehouse),
	`update_time` = NOW() 
WHERE
	`type` = 3
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table data_warehouse
-- ----------------------------
DROP TRIGGER IF EXISTS `update_datd`;
delimiter ;;
CREATE TRIGGER `update_datd` AFTER UPDATE ON `data_warehouse` FOR EACH ROW UPDATE `data_analysis` 
SET 
	-- 数据仓数
	`data_source` = (SELECT COUNT(*) FROM data_warehouse),
	-- 资源项
	`data_table` = (SELECT sum(resources_total) from data_warehouse),
	-- 数据项
	`meta_data` = (SELECT sum(data_item) from data_warehouse),
	-- 数据量
	`application_total` = (SELECT sum(data_total) from data_warehouse),
	`update_time` = NOW() 
WHERE
	`type` = 3
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table data_warehouse
-- ----------------------------
DROP TRIGGER IF EXISTS `delete_data`;
delimiter ;;
CREATE TRIGGER `delete_data` AFTER DELETE ON `data_warehouse` FOR EACH ROW UPDATE `data_analysis` 
SET 
	-- 数据仓数
	`data_source` = (SELECT COUNT(*) FROM data_warehouse),
	-- 资源项
	`data_table` = (SELECT sum(resources_total) from data_warehouse),
	-- 数据项
	`meta_data` = (SELECT sum(data_item) from data_warehouse),
	-- 数据量
	`application_total` = (SELECT sum(data_total) from data_warehouse),
	`update_time` = NOW() 
WHERE
	`type` = 3
;;
delimiter ;