question

akhterhussain-3167 avatar image
0 Votes"
akhterhussain-3167 asked MelissaMa-msft answered

Update trigger is not updating row?

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-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @akhterhussain-3167,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered akhterhussain-3167 commented

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"

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

i could not understand you,

0 Votes 0 ·
TomPhillips-1744 avatar image
1 Vote"
TomPhillips-1744 answered TomPhillips-1744 edited

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.