Deadlock issues with triggers?

The worst thing you were doing was getting an extremely expensive count just to test for existence. This is costly and unnecessary - especially since you were using that to decide if you were going to update OR insert. In a multi-row insert case, you may need to do both.

The worst thing you were doing was getting an extremely expensive count just to test for existence. This is costly and unnecessary - especially since you were using that to decide if you were going to update OR insert. In a multi-row insert case, you may need to do both.

Step 1. Update the ones that exist ;WITH x AS ( SELECT d = DATEADD(DAY, 0, DATEDIFF(DAY, 0, i. TimeOut)), h = DATEPART(HOUR, i.

TimeOut), m = DATEPART(MINUTE, i. Timeout) FROM inserted ), y AS ( SELECT d, h, m, Total = COUNT(*) FROM x GROUP BY d, h, m ) UPDATE h SET Total += y. Total, LastUpdate = CURRENT_TIMESTAMP FROM dbo.

DataWarehouse_HourlyStats AS h INNER JOIN y ON h. Day = y. D AND h.

HourOfTheDay = y. H AND h. MinuteOfTheHour = y.

M; Step 2. Insert the ones that don't ;WITH x AS ( SELECT d = DATEADD(DAY, 0, DATEDIFF(DAY, 0, i. TimeOut)), h = DATEPART(HOUR, i.

TimeOut), m = DATEPART(MINUTE, i. Timeout) FROM inserted ), y AS ( SELECT d, h, m, Total = COUNT(*) FROM x WHERE NOT EXISTS ( SELECT 1 FROM dbo. DataWarehouse_HourlyStats WHERE Day = x.

D AND HourOfTheDay = x. H AND MinuteOfTheHour = x. M ) GROUP BY d, h, m ) INSERT dbo.

DataWarehouse_HourlyStats ( HourOfTheDay, MinuteOfTheHour, Day, Total ) SELECT h,m,d,Total FROM y; It looks like more code but I assure you this is more efficient and more accurate than your existing version. That said, it is going to be a pretty expensive trigger regardless if you have a lot of inserts. I hope at the very least there is a good supporting index on the Day, Hour, Minute columns.

Maybe better to compile the stats using an hourly or daily batch job. The trigger itself is part of the transaction by definition, so you cannot delay the commit unless you just stuff the data into a queue or background table and have some other job fix it.

While this is solid advice it doesn't address the subject of my question (deadlock) at all. – sJhonny Sep 6 at 7:43 actually it does, after indexing, the best way to start attacking deadlocks is to shorten the transaction time. – SqlACID Sep 6 at 10:50 Absolutely, I eliminated one expensive part of the transaction and I've actually seen this exact type of change eliminate deadlocks before.It wasn't in a trigger but they were checking a count first to decide whether to update or insert.

Got rid of the count, got rid of the deadlocks. – Aaron Bertrand Sep 6 at 12:19 actually we did that + added some missing indices to the relevant table (silly DBAs...) and that seemed to do the trick. – sJhonny Sep 6 at 14:17.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.