T-SQL trigger for multiple items - multiple updates does not work as i expected

Guido Bijl 0
Viewed 28

Excuse my sql code as my experience is still somewhat lacking.

I have a trigger to update data in a column of another table even if multiple inserts are made. The trigger works but only for 1 item even if i insert two records. I hade added a select of the memotemp and a select of the trigger query and they show the number of items i expect (2 records for the memotemp table) and (1 records for a select of the trigger query, because 1 order number is added to the memo field and there for it still shows the select of 1 records because of the where statement) just as i expected. What stumps me if why it doesn't update the memo column twice if it has 2 records. The method of joining the inserted with the appropriate table for a delete trigger works perfectly, always deletes as many records as expected one or several at once.

This is the trigger code.

-- insert production order number into purchase order internal memo
select 
    i.vrr_order_id, i.purchase_order_id
into #memotemp
from inserted i
update po
    set po.internal_memo = isnull(po.internal_memo, '') + 
        case when po.internal_memo is null then (select convert(nvarchar(20), vo.order_number)
                                                 from vrr_order vo 
                                                 where vo.vrr_order_id = t.vrr_order_id)
                                           else (po.internal_memo + ' ' + (select convert(nvarchar(20), vo.order_number)
                                                                           from vrr_order vo 
                                                                           where vo.vrr_order_id = t.vrr_order_id))
                                           end
from purchase_order po  
join #memotemp t on po.purchase_order_id = t.purchase_order_id
where isnull(po.internal_memo, '') not like '%' + 
        convert(nvarchar(20), (select vo.order_number 
                               from vrr_order vo 
                               where vo.vrr_order_id = t.vrr_order_id)) + '%'
updates triggers tsql sql
1 Answers

I'm not sure exactly what you are trying to achieve, nor exactly how your tables relate to each other, but your query can be written much more simply

update po
set po.internal_memo =
    concat(
      po.internal_memo,
      po.internal_memo + ' ',
      vo.order_number
    )
from purchase_order po
join inserted t on po.purchase_order_id = t.purchase_order_id
join vrr_order vo on vo.vrr_order_id = t.vrr_order_id
where isnull(po.internal_memo, '') not like '%' + convert(nvarchar(20), vo.order_number) + '%';

Assuming your join condition is not unique, you cannot aggregate using multiple rows in a joined update. SQL Server will only use a single row to update, and ignore the rest. Exactly which row it uses is arbitrary. This is documented here.

Instead, aggregate first. I'm guessing here, because I don't have your table schema

update po
set po.internal_memo = vo.order_number
from purchase_order po
join inserted t on po.purchase_order_id = t.purchase_order_id
cross apply (
    select
      order_number = string_agg(vo.order_number, ' ')
    from vrr_order vo
    where vo.vrr_order_id = t.vrr_order_id
    group by vo.vrr_order_id
) vo
where isnull(po.internal_memo, '') not like '%' + convert(nvarchar(20), vo.order_number) + '%';
Charlieface0
Related