Re: Situation 6
I don't get the use of variables and table type variables for what is essentially a set-based validation:
IF EXISTS (
SELECT external_item_cd
FROM inserted
WHERE external_item_cd IS NOT NULL
GROUP BY external_item_cd
HAVING COUNT(*) > 1
)
BEGIN
RAISERROR('Duplicate Records', 16, 1)
rollback
Return
END
Same with the 2nd step, why count to compare counts, when all you need to test for is existence
IF EXISTS (
SELECT 1
FROM Item As t1
INNER JOIN inserted As i
ON ( i.external_item_cd = t1.external_item_cd )
WHERE EXISTS (
SELECT 1
FROM Item As t2
WHERE t2.external_item_cd = t1.external_item_cd
AND t2.ItemID <> t1.ItemID
)
)