Update Statement within an Insert trigger

[klewis10367]

I am trying to create a trigger that will update fields of the inserted data based on another table. I am getting the trigger to fire but it is not updating the last record that is being inserted. Any ideas?

ALTER TRIGGER [dbo].[SOP10200_kml] ON [dbo].[SOP10200]  after  insert
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
    UPDATE dbo.SOP10200 set  dbo.SOP10200.ITEMDESC = RTRIM(dbo.B4602240.BSSI_Description) + '-' + CAST(MONTH(dbo.B4602243.STRTDATE) AS varchar(2)) + '/' + CAST(DAY(dbo.B4602243.STRTDATE) AS varchar(2))
FROM         dbo.B4602243 INNER JOIN
                      dbo.B4602240 ON dbo.B4602243.BSSI_Tenant_Lease_Number = dbo.B4602240.BSSI_Tenant_Lease_Number AND 
                      dbo.B4602243.LOCNCODE = dbo.B4602240.LOCNCODE AND dbo.B4602243.LNITMSEQ = dbo.B4602240.LNITMSEQ AND 
                      dbo.B4602243.BSSI_Charge_ID = dbo.B4602240.BSSI_Charge_ID INNER JOIN
                      dbo.SOP10200 ON dbo.B4602243.SOPTYPE = dbo.SOP10200.SOPTYPE AND dbo.B4602243.SOPNUMBE = dbo.SOP10200.SOPNUMBE
           
END

 

[RiverGuy]

It looks like your trigger will be updating every single row in your table each time a row is inserted. You probably want to change this to just update the rows which were inserted. You can accomplish this by joining your table being updated to the INSERTED table on your primary/unique key.

 

[klewis10367]

I actually dont mind the update as it is not many records and it will accomodate any changes that are made.

 

[RiverGuy]

It's still against best practices, and the solution is so simple, why not implement it?

But regardless, the only thing I can think of as to why your newly inserted row is not being updated is that you simply do not have any matches for the row in your other tables being joined to.

Copy your code, change the UPDATE to a SELECT *, and run it in a query window. I'm betting you'll get fewer rows returned than exist in your table.