Ejemplo propio para actualizar campo buscando valor en otra tabla:
USE [Lote]
GO
/****** Object: Trigger [dbo].[PonLineaDesvio] Script Date: 22/04/21 0:36:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Name
-- Create date:
-- Description:
-- =============================================
ALTER TRIGGER [dbo].[PonLineaDesvio]
ON [dbo].[Lote]
AFTER INSERT,UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Update Lote set LineaDesvio=
(SELECT TP.Linea FROM Maestros.dbo.TiposArreglos As TP WHERE Lote.Incidencia = TP.Cod)
From Lote, Inserted
Where Lote.Id=Inserted.Id
-- Insert statements for trigger here
END
Fuente: https://soportesql.wordpress.com/2013/02/21/clasico-trigger-para-actualizar-inventario/