存储过程以更新排序编号 [英] Stored Procedure to update the sort numbers

问题描述

HI全部





有人可以帮我这个。



我有一个问题编号为1,2,3 ..... 10的列问题。

此列不是主键。

我的表：



BokID（PK）不是空问题



1 1问题1

2 2问题2

.....

.....

....




$ b $ 10 10 10题10







如果其中一个问题被删除，那么QuesId列应该更新顺序号。





非常感谢

HI All


Can someone help me for this please.

I have a column QuesId with question numbers 1,2,3.....10 .
This column is not a primary key.
My Table:

BokID (PK) not Null QuesId Question

1 1 Question1
2 2 Question2
.....
.....
....


10 10 Question10



And if one of the question is deleted then QuesId column should get updated with sequencial numbers.


Many Thanks

推荐答案

你好。

你应该使用触发来实现这个目标。

试试这个：

Hello.
You should cteate trigger to achieve this goal.
Try this one:

CREATE TRIGGER [dbo].[trg_DeleteQuestion]
    ON  [dbo].[tasks]
 AFTER DELETE
    AS 
BEGIN
    SET NOCOUNT ON;

    MERGE tasks AS t
    USING (SELECT ROW_NUMBER() OVER(ORDER BY BokID) AS position,
                  tasks.*
                  FROM tasks
         ) AS s
        ON t.BokID = s.BokID
      WHEN MATCHED
      THEN UPDATE SET t.QuesID = s.position;
END

upd

如果我们有一个额外的列，那么我们应该为它编写附加条件。我们还应该使用DELETED表，其中包含已删除的行。

upd
If we have an additional column, then we should write additional condition for it. We should also use DELETED table, which contains deleted row.

CREATE TRIGGER [dbo].[trg_DeleteQuestion]
    ON  [dbo].[tasks]
 AFTER DELETE
AS 
BEGIN
	SET NOCOUNT ON;

	MERGE tasks AS t
	USING (SELECT ROW_NUMBER() OVER(ORDER BY tasks.BokID, tasks.Stage) AS position,
	              tasks.*
	         FROM tasks
	         JOIN DELETED AS d
	           ON d.Stage = tasks.Stage
	      ) AS s
	   ON t.BokID = s.BokID AND
	      t.Stage = s.Stage
	 WHEN MATCHED
     THEN UPDATE SET t.QuesID = s.position;
END

欲了解更多信息，请参阅

触发器 [ ^ ]

合并条款 [ ^ ]

祝你好运

For more information please see
Triggers[^]
Merge clause[^]
Good luck

这篇关于存储过程以更新排序编号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！