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
