0

SQL Trigger is not updating all the inserted rows, only few rows get updated,I am not able to understand why. The rows are are getting row ID and RateID, I have tested the trigger by making rowId entries in the Audit log

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[mytrigger]
   ON  [dbo].[myTable]
   AFTER INSERT
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

     DECLARE    @RowId int,
                @RateID int,
                @RateCode NVARCHAR(50)

 SELECT @RateID = RateID,             
            @RowId = RowId
     FROM inserted

    // Method I found on StackOverflow
    --   Update cr
    --   Set cr.RateCode = r.RateCode
    --   From mytable cr
    --   inner join inserted on cr.RowId = inserted.RowId
    --   inner join rates r on inserted.RateID = r.ID
       --where cr.RowId = inserted.RowId

      //Method Aaron Suggested
--UPDATE t1
  --SET t1.RateCode = t2.RateCode
  --FROM dbo.mytable AS t1
 -- INNER JOIN dbo.rates AS t2
 -- ON t1.RateID = t2.ID
 -- WHERE t1.RowId = inserted.RowId

    // Method I am currently using
     IF @RateID IS NOT NULL
     BEGIN
      Select @RateCode = RateCode From rates where ID = @RateID
       IF @RateCode IS NOT NULL      
         Update mytable Set RateCode = @RateCode Where RowId = @RowId
            --UPDATE [dbo].[mytable ] SET  RateCode = @RateCode FROM Inserted i WHERE mytable .RowId = i.RowId
     END
END
Tushar Narang
  • 1,997
  • 3
  • 21
  • 49
  • 1
    You're assigning a single, arbitrary row from inserted to a variable. Try using [an update with a join](http://stackoverflow.com/a/1604212/61305) between `inserted` and the target table. – Aaron Bertrand Mar 19 '17 at 18:08
  • `inserted` can contain zero, one or *multiple* rows. Stop treating it as a source of a *single* piece of data – Damien_The_Unbeliever Mar 19 '17 at 18:08
  • Implying that using single variable like RateId or RowId will not work as you really have multiple rows. So your solution will not use variables like you have it written currently - use the update with join as in first comment. – Sql Surfer Mar 19 '17 at 18:10
  • I have used the join, but that is still not working... Can u tell me what's wrong with my inner join query – Tushar Narang Mar 19 '17 at 18:12
  • @AaronBertrand Plezse see my updated question. How can i get the inserted row id in the last statement – Tushar Narang Mar 19 '17 at 18:19
  • What troubles me more is when i Inserted in audit log, I got rowID and rateID for all rows which got inserted, But value did not get updated in my table – Tushar Narang Mar 19 '17 at 18:25
  • The join in your update doesn't actually reference inserted properly. – Aaron Bertrand Mar 19 '17 at 18:29
  • Yes, how to reference it properly – Tushar Narang Mar 19 '17 at 18:33
  • What is think the issue is with the Select Query, The select query from rates table is returning null, even when it has values – Tushar Narang Mar 19 '17 at 19:52
  • change `WHERE t1.RowId = inserted.RowId` to `INNER JOIN inserted AS I ON I.RowID = t1.RowID` for the method Aaron suggested. – ZLK Mar 19 '17 at 21:49

0 Answers0