I have encountered a situation that causes duplicate records - which, in turn, causes difficulties in billing. So I decided to alter/change the AccountData table with UNIQUE constraints to repel the duplicate records. That solution worked fine until the Error Log filled up.
So I got wind of an idea: someone mentioned using a TRIGGER (FOR INSERT, I beleive) to "flag those duplicate records as duplicates". But it turns out to be easier said than done.
Now I'm at an impasse: I have no idea how to get the trigger to check for duplicates, and flag the incoming record as duplicate when it turns out to BE a duplicate. I figure overloading a column will suffice (to detect the dupes at billing time), but avoiding self-reference has been a real BEAR.
Does anybody have any idea how I could tackle this beast?
Thank you (in advance),
-LongFist
ALTER TABLE AccountData
ADD DuplicateEntry BIT DEFAULT 0
GO
Then, add a trigger something like below. Naturally you will need to replace col1 ... col7 with the names of your seven (or however many) determinant columns.
You will need an index on the controlling columns if the table is large and should probably add one anyway, just in case, although it will be a large index since it will have so many columns.
CREATE TRIGGER AccountData_Trig01_ChkDups
ON AccountData
INSTEAD OF INSERT
AS
INSERT INTO AccountData
SELECT col1, col2, col3, col4, col5, col6, col7, ...allOtherColumnsExceptBi
CASE WHEN EXISTS(
SELECT 1
FROM AccountData AD2
WHERE AD2.col1 = AccountData.col1
AND AD2.col2 = AccountData.col2
AND AD2.col3 = AccountData.col3
AND AD2.col4 = AccountData.col4
AND AD2.col5 = AccountData.col5
AND AD2.col6 = AccountData.col6
AND AD2.col7 = AccountData.col7
) THEN 1 ELSE 0 END
FROM inserted
Note: this code assumes SQL 2K. If on SQL 7.0, you will have to use an UPDATE in an AFTER INSERT trigger, since INSTEAD OF is not available in 7.0.