IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')
BEGIN
    DROP TABLE tba
END
GO
CREATE TABLE tba
(
    ID INT,
    PID INT,
    name VARCHAR(100)
)
GO

CREATE TRIGGER TR_Tba_Delete ON tba FOR DELETE
AS
BEGIN

    DECLARE @Delete TABLE(TID INT IDENTITY(1,1),ID INT,PID INT,Name VARCHAR(100))
    DECLARE @line INT
    DECLARE @Total INT
    
    SET @line = 1
    
    INSERT INTO @Delete
    SELECT ID,PID,Name
    FROM deleted
    ORDER BY ID,PID DESC
    
    SELECT @Total = COUNT(1) FROM @Delete
    
    WHILE @line <= @Total
    BEGIN
    
        UPDATE tba SET tba.PID = tba.PID - 1 FROM @Delete AS A WHERE a.ID = tba.ID AND a.PID < tba.PID AND a.TID = @line
        
        SET @line = @line + 1
    
    END
    
    

END
GO



INSERT INTO tba
SELECT 1, 1, 'www' UNION
SELECT 1, 2, 'fffff' UNION
SELECT 1, 3, 'aaa' UNION
SELECT 1, 4, 'cccc' UNION
SELECT 2, 1, 'vv' UNION
SELECT 2, 2, 'tttt'

SELECT * FROM tba
ID    PID    name
1    1    www
1    2    fffff
1    3    aaa
1    4    cccc
2    1    vv
2    2    tttt

DELETE FROM tba WHERE name IN ('aaa','vv')

SELECT * FROM tba
ID    PID    name
1    1    www
1    2    fffff
1    3    cccc
2    1    tttt

------解决方案--------------------

SQL code

CREATE TABLE t1
(
    id INT,
    pid INT,
    name VARCHAR(10)
)
INSERT INTO t1
SELECT 1,    1,    'www' UNION ALL
SELECT 1,    2,    'fffff' UNION ALL
SELECT 1,    3,    'aaa' UNION ALL
SELECT 1,    4,    'cccc' UNION ALL
SELECT 2,    1,    'vv' UNION ALL
SELECT 2,    2,    'tttt'
SELECT * FROM t1

DELETE FROM t1 WHERE id=1 AND pid IN (2,3)

SELECT id,ROW_NUMBER() OVER(PARTITION BY id ORDER BY pid) AS pid,name  FROM t1

------------------------
id    pid    name
1    1    www
1    2    cccc
2    1    vv
2    2    tttt