Update Child Id after updating Parent Id using Trigger in SQL Server

Hi irshad.niet,

Refer the below query. Use AFTER UPDATE trigger.

SQL

CREATE TABLE [collection]
(
    [id] INT PRIMARY KEY,
    [type] VARCHAR(20),
    [parent] INT
)
INSERT INTO [collection] VALUES (0,'collection',Null);
INSERT INTO [collection] VALUES (1,'collection',0);
INSERT INTO [collection] VALUES (2,'collection',1);
INSERT INTO [collection] VALUES (3,'product',4);
INSERT INTO [collection] VALUES (4,'collection',0);
INSERT INTO [collection] VALUES (5,'product',2);
INSERT INTO [collection] VALUES (6,'product',4);

Trigger

CREATE TRIGGER [dbo].[tr_SCHEDULE_Modified]
   ON [dbo].[collection]
   AFTER UPDATE
AS 
BEGIN
	SET NOCOUNT ON;
    DECLARE @OldId INT, @NewId INT
	-- Get Updated value from INSERTED
    SELECT @NewId = INSERTED.id FROM INSERTED
	-- Get Old value from DELETED
	SELECT @OldId = DELETED.id FROM DELETED

	UPDATE [collection] SET [parent] = @NewId WHERE [parent] = @OldId
END

Execute the query.

DECLARE @Id INT = 4
DECLARE @IdUpdate INT = 7
UPDATE [collection] SET [id] = @IdUpdate WHERE [id] = @Id
SELECT * FROM [collection]

Record after update