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
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