Update trigger is not updating row?

Analyst_SQL 3,551 Reputation points
2021-06-24T12:08:24.17+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,884 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
0 comments No comments
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-06-25T06:01:43.127+00:00

    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.

    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Tom Phillips 17,716 Reputation points
    2021-06-24T16:48:20.837+00:00

    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
    
    1 person found this answer helpful.
    0 comments No comments

  2. Olaf Helper 41,101 Reputation points
    2021-06-24T12:41:28.67+00:00

    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"