自动按组存取数据
背景:
有表[tblA]记录产品条码信息如:
tid sno
1 00011P2112
2 00021P2112
3 00031P2112
4 00041P2112
5 00081P2112
6 00101P2112
7 00111P2112
8 00151P2112
... ...
有表[tblB]记录产品的分子信息,列有分组id,产品条码,生成时间,...,需要在tblA表中每生成一行记录时,tblB中自动记录tblA的信息,
具体体现如下(示例以两个产品一组建立个虚拟分组号,当前虚拟分组号以产品的流水号部分体现;此处视产品的前四位为唯一流水号):
pkgid sno dinsert
0001 00011P2112 2020-09-19 20:08:31.150
0001 00021P2112 2020-09-19 20:08:49.527
0003 00031P2112 2020-09-19 20:09:01.577
0003 00041P2112 2020-09-19 20:09:12.800
0008 00081P2112 2020-09-19 20:09:24.490
0008 00101P2112 2020-09-19 20:10:30.727
0011 00111P2112 2020-09-19 20:10:44.697
0011 00151P2112 2020-09-19 20:10:54.807
... ... ...
触发器实现,简单sql脚本如下:
USE [TestDB] GO /****** Object: Trigger [dbo].[tri_insert_tblA] Script Date: 2020/9/19 19:34:31 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [dbo].[tri_insert_tblA] ON [dbo].[tblA] AFTER INSERT AS BEGIN DECLARE @pkgid NVARCHAR(50) --当前产品条码 DECLARE @pkgidPrevious NVARCHAR(50) --上一个产品条码部分(示前四位为唯一流水号) DECLARE @x INT --分组数量设定,当前设置为两个一组 SELECT @pkgid=SUBSTRING(sno,1,4) FROM Inserted SELECT @pkgidPrevious=sno FROM ( SELECT ROW_NUMBER()OVER(ORDER BY dinsert DESC) sn,SUBSTRING(sno,1,4) sno FROM tblB )a WHERE a.sn=1 SELECT @x=cc FROM ( SELECT DISTINCT COUNT(pkgid)OVER(PARTITION BY pkgid)cc FROM tblB WHERE pkgid=@pkgidPrevious)a IF @x>0 AND @x < 3 BEGIN INSERT INTO tblB(pkgid,sno) SELECT @pkgidPrevious,sno FROM inserted END ELSE BEGIN INSERT INTO tblB(pkgid,sno) SELECT @pkgid,sno FROM inserted END END