EXCEPT in a trigger

Question

Post reply

EXCEPT in a trigger

terry999

SSCertifiable

Points: 5033
More actions
September 8, 2017 at 8:37 am

#339054

Hi
I have an Update trigger using binary_checksum to determine differences in many fields.
It's a SQL 2005 DB, which I don't have now, I'm checking it on 2008 to see if using EXCEPT is feasible. But it will need to be deployed on 2005.
The Trigger has missed some changes out, I cannot reproduce it. I've read that binary_checksum is unreliable.

SELECT PKID, f1,f2,f3,f4,f5,f6....
INTO #t
FROM
( SELECT PKID, binary_checksum(f1,f2,f3,f4,f5,f6....) as DChk from deleted) as Del
INNER JOIN
(SELECT PKID, binary_checksum(f1,f2,f3,f4,f5,f6....) as IChk from inserted) as Ins
ON del.PKID = Ins.PKID
WHERE Del.DChk <> Ins.IChk
-- Audit these changed values and perform other stuff
I'm considering changing the SQL that populates the temp table #t
SELECT PKID, f1,f2,f3,f4,f5,f6.... INTO #t FROM Inserted as Ins EXCEPT SELECT PKID, f1,f2,f3,f4,f5,f6.... FROM Deleted -- Audit these changed values and perform other stuff
I've never used EXCEPT is there anything I should be aware of?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 1

Yep, you cannot use EXCEPT on a 2005 instance. The feature wasn't introduced until SQL Server 2008.

Set Operators - EXCEPT and INTERSECT (Transact-SQL)

Joie Andrew
"Since 1982"

ScottPletcher SSC Guru Points: 100946 More actions · Answer 2

Joie Andrew - Sunday, September 10, 2017 3:05 AM
Yep, you cannot use EXCEPT on a 2005 instance. The feature wasn't introduced until SQL Server 2008.
Set Operators - EXCEPT and INTERSECT (Transact-SQL)

Hmm, EXCEPT works just fine on my remaining SQL 2005 instance.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 3

My mistake! Weird that the Microsoft documentation lists that EXCEPT starts with SQL Server 2008 but it is listed elsewhere as starting with 2005.

Joie Andrew
"Since 1982"

Chris Wooding SSCarpal Tunnel Points: 4530 More actions · Answer 4

One other thing to be aware of is that EXCEPT eliminates duplicates.

jcelko212 32090 SSCrazy Eights Points: 9294 More actions · Answer 5

Chris Wooding - Monday, September 11, 2017 7:43 AM
One other thing to be aware of is that EXCEPT eliminates duplicates.

The ANSI/ISO Standard SQL has the EXCEPT ALL option. See if MS is going to catch up.

Please post DDL and follow ANSI/ISO standards when asking for help.

Sue_H SSC Guru Points: 90860 More actions · Answer 6

Joie Andrew - Sunday, September 10, 2017 8:27 AM
My mistake! Weird that the Microsoft documentation lists that EXCEPT starts with SQL Server 2008 but it is listed elsewhere as starting with 2005.

I don't know if it's weird or if it's just typical. Since they consolidated the documentation there are a lot of things like that and it's hard to figure out what pieces of what apply to what versions. Some are really bad. But I think they defaulted everything to "Starts with 2008" if it started with 2008 or earlier when they consolidated the docs.

Sue