jeudi 18 décembre 2014

How to filter out deleted records in a trigger on a table with composite primary key (MSSQL)


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 filter out 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?





Aucun commentaire:

Enregistrer un commentaire