1

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?

4
  • 7
    Oh my, attempted concatenation of two incompatible types, old-style joins, nvarchar without length - there is a whole mess of problems going on here. What exactly are you attempting to do? What does "filter out" mean? What will the trigger do with the rows it identifies? Is this trigger supposed to do different things with an update than a delete? Is it configured as a trigger for both update and delete? For a delete operation, inserted will be empty, so I'm having a real hard time understanding what is really going on here and what purpose this trigger could possibly serve. Dec 18, 2014 at 15:30
  • Sorry for being so unclear, I have replaced "filter out" with "retreive". There are multiple paths for updated, deleted and inserted records. This is a monster of a trigger with multiple nested cursors, temp tables, on-the-fly string-concatinated xml generation and so on. I'm in the process of getting rid of the temp tables as a first step. It's a mind-boggling puzzle I'm trying to solve, without a full understanding of what it is actually supposed to technically. Luckily I have regression tests at my disposal to check if it "still works" functionally after I try applying improvements. Dec 18, 2014 at 22:14
  • 2
    If you're relying on the trigger to SELECT rows and consume them outside of the trigger, you're in for a world of pain, as this will not be supported in a future version of SQL Server. Dec 18, 2014 at 22:17
  • @AaronBertrand I can't really imagine any pain worse than I already have with this trigger ;-) but no it's inserting xml strings into a different database, depending on zillions of conditions I'm trying to reverse engineer at this moment. First I need to simplify it a little in order to fully understand what it is actually doing. Removing temp tables first, and then I hope to be able to get rid of some cursors. I am learning quite allot from this exercise! Dec 18, 2014 at 22:45

1 Answer 1

2

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
8
  • This is just as useless / nonsensical as what the OP is attempting to do. If the operation is an INSERT, the deleted table is empty. For a DELETE operation the inserted table is empty. 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). Dec 18, 2014 at 17:09
  • based on the query he presented, it looks like the trigger is trying to catch deleted rows and is also triggered by updates, or updated rows where one or both parts of the composite primary key changed.
    – SqlZim
    Dec 18, 2014 at 17:23
  • Ok. Good point about updating the PK, which I had not considered because, eeyyww, but still the same number of rows even if some values have changed. Yes, you are correct in understanding and simplifying the OPs query. However, just like the OPs original query, in the case of an UPDATE only rows with a modified PK are returned, but in the case of a DELETE all rows are returned. This still doesn't seem to match up to the stated desire, though that is not your fault as it is very unclear ;-). And regardless, sorry if my tone was harsh. Dec 18, 2014 at 18:29
  • Yes, updating the PK is very eeyyww, but since it is a 3rd party database all bets are off. I think the phrase "filter out" that Louis used was the opposite of what he meant based on the query.
    – SqlZim
    Dec 18, 2014 at 18:42
  • I agree that is seems that Louis's use of "filter out" is the opposite of the actual desire, but need confirmation. And still, not sure if the behavior on a DELETE (returning all rows) is correct. All depends on interpretation. Dec 18, 2014 at 18:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.