I am inserting row in table(#tbl_leaves_Detail) using trigger,now i want to update it ,but update trigger is not working
CREATE TABLE Tbl_Leaves (
L_ID int,
L_From_Date date,
L_To_Date date,
L_Days int
);
CREATE TABLE Tbl_Leaves_Details (
ID int IDENTITY(1, 1) NOT NULL,
L_ID int,
L_Date date,
L_Qty int
);
GO
CREATE TRIGGER TI_Tbl_Leaves ON Tbl_Leaves
AFTER INSERT
AS
BEGIN
;WITH CTE AS (
SELECT L_ID, L_From_Date, L_To_Date
FROM inserted
UNION ALL
SELECT L_ID, DATEADD(day, 1, L_From_Date) AS L_From_Date, L_To_Date
FROM CTE
WHERE L_From_Date < L_To_Date
)
INSERT INTO Tbl_Leaves_Details
SELECT ID,L_ID, L_From_Date, 1 AS L_Qty
FROM CTE;
END
GO
INSERT INTO Tbl_Leaves VALUES (1001, '2021-06-01', '2021-06-05', 5);
GO
SELECT * FROM Tbl_Leaves;
SELECT * FROM Tbl_Leaves_Details;
GO
Below is Update trigger
which is not updating rows in #tbl_Leaves_Detail,
alter TRIGGER [dbo].[TI_Tbl_Leaves_Update] ON [dbo].[tbl_Leaves]
AFTER Update
AS
BEGIN
;WITH CTE AS (
SELECT L_ID, L_From_Date, L_To_Date
FROM Updated
UNION ALL
SELECT L_ID, DATEADD(day, 1, L_From_Date) AS L_From_Date, L_To_Date
FROM CTE
WHERE L_From_Date < L_To_Date
)
Update tbl_Leaves set L_From_Date='2021-06-01',L_To_Date='2021-06-02', L_Days=2 where L_ID=1
END
it i will two row and remaing row ,need to be deleted.