Update trigger is not updating row?

Question

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.

Answer 1

Hi @Analyst_SQL ,

You could also modify the update trigger like below:

CREATE TRIGGER [dbo].[TI_Tbl_Leaves_Update] ON [dbo].[tbl_Leaves]  
AFTER Update   
AS   
BEGIN  
  
DELETE M  
FROM [dbo].Tbl_Leaves_Details M  
INNER JOIN Inserted I  
    ON I.L_ID = M.L_ID;  
  
;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(L_ID,L_Date,L_Qty)  
    SELECT L_ID, L_From_Date, 1 AS L_Qty  
    FROM CTE;  
       
END  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

This is much simpler to do as a MERGE:

 CREATE OR ALTER TRIGGER TI_Tbl_Leaves ON Tbl_Leaves
 AFTER INSERT, UPDATE
 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
      )

 MERGE INTO Tbl_Leaves_Details dest
 USING (
      SELECT L_ID, L_From_Date as L_Date, 1 AS L_Qty
      FROM CTE
 ) src
 ON src.L_ID = dest.L_ID
 AND src.L_Date = dest.L_Date
 AND src.L_Qty = dest.L_QTY

 WHEN NOT MATCHED BY TARGET THEN
 INSERT (L_ID, L_Date, L_Qty) VALUES (src.L_ID, src.L_Date, src.L_Qty)
 WHEN NOT MATCHED BY SOURCE THEN
 DELETE
 ;



 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

  UPDATE tbl_Leaves
 SET L_To_Date = '2021-06-02'
  WHERE L_ID = 1001
  GO

  SELECT * FROM Tbl_Leaves;
  SELECT * FROM Tbl_Leaves_Details;
  GO

  UPDATE tbl_Leaves
 SET L_To_Date = '2021-06-15'
  WHERE L_ID = 1001
  GO

  SELECT * FROM Tbl_Leaves;
  SELECT * FROM Tbl_Leaves_Details;
  GO

Answer 3

SELECT L_ID, L_From_Date, L_To_Date
FROM Updated

In a trigger exists 2 virtual tables: inserted and deleted, but no updated
See Use the inserted and deleted Tables

In an update trigger the data before changes is in virtual table "deleted" and the data after update in "inserted"