如何用SQL语言编写一段使字段[trx_code]自递增的程序

Question

原始表的ID为:1,2,3,4,5,6
纪录表的ID:6
原始表的ID为:1,2, , ,5,6
纪录表的ID:2,6
example:
--建立原始表CREATE TABLE [IDTest] (
[ID] [int] NOT NULL,
[value] [sql_variant] NULL ,
 PRIMARY KEY  CLUSTERED 
(
[ID]
)  ON [PRIMARY] 
) ON [PRIMARY]
GOALTER TABLE [IDtest]
ADD CONSTRAINT Def_IDtest
DEFAULT dbo.fn_GetNewID(OBJECT_ID('IDTest')) 
FOR ID
GO--建立记录表
CREATE TABLE sysIDDiffs(
TableID int,
ID int,
CONSTRAINT PK_sysIDDiffs PRIMARY KEY CLUSTERED (TableID, ID))
GO
--建立UDF
CREATE FUNCTION dbo.fn_GetNewID(
@TableID int)
RETURNS int
AS
BEGIN
DECLARE @ID int

SELECT @ID = MIN(ID)
FROM sysIDDiffs (XLOCK)
WHERE TableID = @TableID
SET @ID = ISNULL(@ID, 0) + 1
RETURN @ID
END

Answer 1

接上贴:
--建立TRIGGER 在IDTEST TABLE
CREATE  TRIGGER udt_IDTest
ON IDTest
AFTER UPDATE, INSERT, DELETE
AS
DECLARE @nInserted int, @nDeleted int -- Obtain how many rows have been deleted. SELECT @nInserted = COUNT(*)
FROM Inserted -- Obtain how many rows have been inserted. SELECT @nDeleted = COUNT(*)
FROM Deleted
IF ISNULL(@nDeleted,0) > 0
BEGIN DELETE sysIDDiffs
FROM sysIDDiffs s
JOIN deleted d
ON d.ID = s.ID
AND s.TableID = OBJECT_ID('IDtest')
IF EXISTS (
SELECT *
FROM IDTest s
JOIN deleted d
ON s.ID = d.ID - 1)
INSERT sysIDDiffs
SELECT OBJECT_ID('IDTest'), 
s.ID
FROM IDTest s
JOIN deleted d
ON s.ID = d.ID - 1 IF EXISTS(
SELECT *
FROM deleted
WHERE ID = 1) INSERT sysIDDiffs
SELECT OBJECT_ID('IDTest'), 0 END


IF ISNULL(@nInserted,0) > 0
BEGIN
DELETE sysIDDiffs
FROM sysIDDiffs s
JOIN inserted i
ON s.ID = i.ID -1
AND s.TableID = OBJECT_ID('IDtest') INSERT sysIDDiffs
SELECT OBJECT_ID('IDtest'), 
ID
FROM inserted i
WHERE NOT EXISTS (
SELECT *
FROM IDTest s
WHERE s.ID = i.ID + 1)
END
GO

Answer 2

--测试结果INSERT IDtest (Value)
VALUES ('2nd row State 3')INSERT IDtest (Value)
VALUES ('3rd row State 3')INSERT IDtest (Value)
VALUES ('4th row State 3')INSERT IDtest (Value)
VALUES ('5th row State 3')INSERT IDtest (Value)
VALUES ('6th row State 3')INSERT IDtest (Value)
VALUES ('7th row State 3')INSERT IDtest (Value)
VALUES ('8th row State 3')INSERT IDtest (Value)
VALUES ('9th row State 3')INSERT IDtest (Value)
VALUES ('10th row State 3')SELECT *
FROM idtestSELECT ID
FROM sysIDDiffs
WHERE TableID = OBJECT_ID('IDtest')/*
 Results after State 3ID          value                                    
----------- ---------------------------------------- 
1.00        State 2
2.00        2nd row State 3
3.00        3rd row State 3
4.00        4th row State 3
5.00        5th row State 3
6.00        6th row State 3
7.00        7th row State 3
8.00        8th row State 3
9.00        9th row State 3
10.00       10th row State 3ID          
----------- 
10.00
*/
GO
-- State 4 Figure 2
INSERT IDtest (Value)
VALUES ('1st row State 4')SELECT *
FROM idtestSELECT ID
FROM sysIDDiffs
WHERE TableID = OBJECT_ID('IDtest')/*
 Results after State 4ID          value                                    
----------- ---------------------------------------- 
1.00        State 2
2.00        2nd row State 3
3.00        3rd row State 3
4.00        4th row State 3
5.00        5th row State 3
6.00        6th row State 3
7.00        7th row State 3
8.00        8th row State 3
9.00        9th row State 3
10.00       10th row State 3
11.00       1st row State 4ID          
----------- 
11.00*/
GO
-- State 5 Figure 2
DELETE IDtest
WHERE ID IN (3, 5, 8, 9)SELECT *
FROM idtestSELECT ID
FROM sysIDDiffs
WHERE TableID = OBJECT_ID('IDtest')/*
 Results after State 5ID          value                                    
----------- ---------------------------------------- 
1.00        State 2
2.00        2nd row State 3
4.00        4th row State 3
6.00        6th row State 3
7.00        7th row State 3
10.00       10th row State 3
11.00       1st row State 4ID          
----------- 
2.00
4.00
7.00
11.00*/
GO
-- State 6 Figure 2INSERT IDtest (Value)
VALUES ('1st row State 6')SELECT *
FROM idtestSELECT ID
FROM sysIDDiffs
WHERE TableID = OBJECT_ID('IDtest')/*
 Results after State 6ID          value                                    
----------- ---------------------------------------- 
1.00        State 2
2.00        2nd row State 3
3.00        1st row State 6
4.00        4th row State 3
6.00        6th row State 3
7.00        7th row State 3
10.00       10th row State 3
11.00       1st row State 4ID          
----------- 
4.00
7.00
11.00*/
GO
-- State 7 Figure 3
INSERT IDtest (Value)
VALUES ('1st row State 7')SELECT *
FROM idtestSELECT ID
FROM sysIDDiffs
WHERE TableID = OBJECT_ID('IDtest')/*
 Results after State 7ID          value                                    
----------- ---------------------------------------- 
1.00        State 2
2.00        2nd row State 3
3.00        1st row State 6
4.00        4th row State 3
5.00        1st row State 7
6.00        6th row State 3
7.00        7th row State 3
10.00       10th row State 3
11.00       1st row State 4ID          
----------- 
7.00
11.00*/GO-- State 8 Figure 3INSERT IDtest (Value)
VALUES ('1st row State 8')SELECT *
FROM idtestSELECT ID
FROM sysIDDiffs
WHERE TableID = OBJECT_ID('IDtest')/*
 Results after State 8ID          value                                    
----------- ---------------------------------------- 
1.00        State 2
2.00        2nd row State 3
3.00        1st row State 6
4.00        4th row State 3
5.00        1st row State 7
6.00        6th row State 3
7.00        7th row State 3
8.00        1st row State 8
10.00       10th row State 3
11.00       1st row State 4ID          
----------- 
8.00
11.00
*/
GO
-- State 9 Figure 3
INSERT IDtest (Value)
VALUES ('1st row State 9')SELECT *
FROM idtestSELECT ID
FROM sysIDDiffs
WHERE TableID = OBJECT_ID('IDtest')/*
 Results after State 9ID          value                                    
----------- ---------------------------------------- 
1.00        State 2
2.00        2nd row State 3
3.00        1st row State 6
4.00        4th row State 3
5.00        1st row State 7
6.00        6th row State 3
7.00        7th row State 3
8.00        1st row State 8
9.00        1st row State 9
10.00       10th row State 3
11.00       1st row State 4ID          
----------- 
11.00
*/
GO