在触发动作后在表中获得相同的值

问题描述

我需要生成一个ID列，该列会自动递增，并且我想用此列连接另一列

我编写了以下触发器，但对于表中的每个插入，它总是返回相同的值

例如，每次插入ex.Document_Id = MGA0001

i need to generate one id column which increments automatically and i want concatenate another column with this column

i written the following trigger but it always returning the same value for every insertion in the table

ex.Document_Id = MGA0001 for every insertion

ALTER TRIGGER Document_Management_Trigger ON Document_Management AFTER INSERT
AS
BEGIN
DECLARE @ID INT = 0
DECLARE @Document_Type INT = 0
DECLARE @Doc_Type NVarchar(2) = NULL
DECLARE @Autogenerate_Id NVarchar(10) = NULL
DECLARE @New_Autogenerate_Id NVarchar(10) = NULL
DECLARE @Character CHAR(1) = NULL
DECLARE @Number INT = 0


SELECT @ID = I.ID FROM INSERTED I;

SELECT @Document_Type = I.Document_Type FROM INSERTED I;
SELECT @Doc_Type = I.Doc_Type FROM INSERTED I;
DECLARE @Document_Mana INT = 0

SET @Autogenerate_Id = (SELECT TOP 1 Autogenerate_ID FROM Document_Management DM WHERE ID = (SELECT MAX(ID) FROM Document_Management WHERE Document_Type = @Document_Type))

IF @Autogenerate_Id IS NULL
BEGIN
    SET @Autogenerate_Id = 'A0000'
END

SET @Character = (SELECT SUBSTRING (@Autogenerate_Id ,1, 1))

SET @Number = (SELECT SUBSTRING (@Autogenerate_Id ,2, 4))

SET @Number = @Number + 1

IF @Number > 9999
BEGIN
   SET @Number = 1

   SET @Character = CHAR(ASCII(@Character)+1)
END

SET @Autogenerate_Id = @Character + RIGHT(CONVERT(VARCHAR(4),@Number),4)


UPDATE Document_Management SET Autogenerate_Id = @Autogenerate_Id, Document_Id = @Doc_Type + @Autogenerate_Id WHERE ID = @ID

END

推荐答案

SET @Autogenerate_Id = (SELECT TOP 1 Autogenerate_ID FROM Document_Management DM WHERE ID = (SELECT MAX(ID) FROM Document_Management WHERE Document_Type = @Document_Type))

IF @Autogenerate_Id IS NULL
BEGIN
    SET @Autogenerate_Id = 'A0000'
END

对于特定的@Document_Type，变量@Autogenerate_Id始终以NULL的形式出现.检查表Document_Management中Autogenerate_ID value的值，然后尝试检查此列的非null值.这样就可以了.

希望对您有所帮助.

The variable @Autogenerate_Id is always coming as NULL for that particular @Document_Type. check the value of Autogenerate_ID value in table Document_Management, and try check for non null values of this column. Then it may work.

Hope it helps.

这篇关于在触发动作后在表中获得相同的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！