How to retreive deleted records in a trigger on a table with composite primary key

Question

I am trying to optimize a trigger on a table in a database from a 3rd party. I am not allowed to change or add any columns to the schema.

The table has a composite primary key, one column is an integer, the other is nvarchar.

How can I retreive the deleted rows without using a temp table?

I am currently concatinating the keys in a string in the where clause like this:

SELECT FROM deleted WHERE (deleted.a + CAST(deleted.b as nvarchar) 
       NOT IN (SELECT deleted.a + CAST(deleted.b as nvarchar) 
       FROM inserted, deleted 
       WHERE inserted.a = deleted.a 
         and inserted.b = deleted.b))

I have also tried several variants of deleted.%%physloc%% kind of attempts, but with no luck. Is there any more elegant way to filter out the deleted records?

Answer 1

If I'm reading that right, this should work:

select
  from deleted d
  where not exists (select 1 from inserted i where i.a=d.a and i.b=d.b)

---

Regarding srutzky's comment

"And for an UPDATE, both inserted and deleted tables have the exact same rows in them (as you cannot update a row that did not exist)."

This is not true in the case of updatable primary keys.

create table dbo.srutzky (a int not null
  , b nvarchar(44) not null
  , c bit not null
  , constraint pk_srutzky primary key clustered (a,b)
)

insert into dbo.srutzky (a,b,c) values
 (1,'for an UPDATE',1)
,(2,'both inserted and deleted',1)
,(3,'have the exact same rows in them ',1)

update dbo.srutzky 
  set a=4
    , b = 'might not have the exact same rows in them' 
  output deleted.a as deleted_a
       , deleted.b as deleted_b
       , inserted.a as inserted_a
       , inserted.b as inserted_b
  where a = 3

Results

deleted_a   deleted_b                                    inserted_a  inserted_b
----------- -------------------------------------------- ----------- --------------------------------------------
3           have the exact same rows in them             4           might not have the exact same rows in them