I have the following scenario:

TOS

TOS_AUX

I want to place a trigger on Table 1, that updates table 02 Description with a concat:

table 1. field 1 , ' - ',table1.field2,' - ',table2.description

My problem is how to "mention" table 2 "same line" in the concat

Here is what I have:

ALTER TRIGGER [dbo].[atualizaPKnoUpdate] 
ON  [dbo].[TOS] 
   AFTER update
 AS 
 BEGIN
    
    SET NOCOUNT ON;

   
    declare
    @Servico int,
    @PK float,
    @TipoPK varchar(200)

    select
    @servico = codigo,
    @pk = U_PICKING_3,
    @TipoPK = U_TIPO_REFERENCIA

    FROM inserted

    UPDATE DBO.TOS_AUX
    SET U_PICKING_3 = @PK,
    U_TIPO_DOC_REFERENCIA = @TipoPK

    WHERE
    TOS_AUX.COD_OS=@Servico
    AND TOS_AUX.U_PICKING_3 IS NULL
    

    UPDATE DBO.TOS_AUX
    SET 
    subtitulo = CONCAT(@TipoPK,'-',@PK,' - ',--this part is missing--)

    WHERE
    TOS_AUX.COD_OS=@Servico
    
END

So, for

UPDATE TOS
SET U_TIPO_REFERENCIA = 'PV'
WHERE CODIGO = 1

I expect to have:

TOS

TOS_AUX

Thanks in advance.

CodePudding user response：

Ignoring whether or not your approach is correct for the moment, I believe you just need a JOINed UPDATE e.g. something similar to the following where you join the Inserted pseudo-table onto the table you want to update and then conditionally update as required. I have done the best I can to match your existing logic, but you'll have to test it. Note this also solves your issue of handling multiple rows being updated at the same time.

UPDATE AUX SET
    U_PICKING_3 = CASE WHEN AUX.U_PICKING_3 IS NULL THEN I.U_PICKING_3 ELSE AUX.U_PICKING_3 END
    , U_TIPO_DOC_REFERENCIA = CASE WHEN AUX.U_PICKING_3 IS NULL THEN I.U_TIPO_REFERENCIA ELSE AUX.U_TIPO_REFERENCIA END
    , Subtitulo = CONCAT(I.U_TIPO_REFERENCIA, '-', U_PICKING_3, ' - ', AUX.Subtitulo)
FROM DBO.TOS_AUX AUX
INNER JOIN Inserted I ON I.codigo = TOS.COD_OS;

However for the sake of completeness, it would be remiss of me not to mention that this is most likely a bad approach. One should avoid storing the same data in multiple locations at all costs because it leads to all sorts of issues that are easily avoided by maintaining the data in a single location and querying it as required.