22,207
社区成员
发帖
与我相关
我的任务
分享
--code name level type_code
--1001 三 1 D
--1003 七 2 D
--2001 四 1 C
--2002 八 2 C
--3001 五 2 B
--4001 六 3 D
use Tempdb
go
--> --> 中国风(Roy)生成測試數據
if not object_id(N'Test1') is null
drop table Test1
Go
Create table Test1([code] int,[name] nvarchar(21),[level] int,[type_code] nvarchar(21))
GO
if not object_id(N'SerialNumber') is null
drop table SerialNumber
Go
CREATE TABLE SerialNumber(
TypeCode VARCHAR(5) PRIMARY KEY
,TypeNr VARCHAR(2) UNIQUE
,NextNr INT
,NrLength INT
);
GO
CREATE TRIGGER tr_Test1 ON Test1
INSTEAD OF INSERT
AS
SET NOCOUNT ON;
BEGIN
DECLARE @TypeNr INT
SELECT @TypeNr=ISNULL(MAX(TypeNr),0) FROM dbo.SerialNumber
IF EXISTS(SELECT 1 FROM inserted AS i WHERE NOT EXISTS(SELECT 1 FROM dbo.SerialNumber WHERE TypeCode=i.type_code) )
BEGIN
INSERT INTO dbo.SerialNumber
( TypeCode, TypeNr, NextNr, NrLength )
SELECT
type_code,@TypeNr+ROW_NUMBER()OVER(ORDER BY RAND()),1,3
FROM Inserted
GROUP BY type_code
END
INSERT INTO dbo.Test1
( code, name, level, type_code )
SELECT
s.TypeNr+RIGHT(100000+s.NextNr+ROW_NUMBER()OVER(PARTITION BY i.type_code ORDER BY i.level)-1,s.NrLength),
i.name,
s.NextNr+ROW_NUMBER()OVER(PARTITION BY i.type_code ORDER BY i.level)-1
,i.type_code
FROM Inserted AS i
INNER JOIN dbo.SerialNumber AS s ON i.type_code=s.TypeCode
UPDATE s SET s.NextNr=s.NextNr+i.con FROM (SELECT type_code,COUNT(1) AS con FROM Inserted GROUP BY type_code) AS i INNER JOIN dbo.SerialNumber AS s ON i.type_code=s.TypeCode
END
GO
--测试
Insert Test1(name,type_code)
select N'三',N'D' union all
select N'七',N'D'
Insert Test1(name,type_code)
SELECT N'四',N'C' union all
select N'八',N'C'
Insert Test1(name,type_code)
select N'五',N'B'
Insert Test1(name,type_code)
SELECT N'六',N'D'
--查看
SELECT * FROM dbo.Test1
/*
code name level type_code
1001 三 1 D
1002 七 2 D
2001 四 1 C
2002 八 2 C
3001 五 1 B
1003 六 3 D
*/
--删除测试
--DROP TABLE test1,dbo.SerialNumber