Trigger to prevent Duplicates

[nolesce]

I am working with a third party program that sends data and files to a web based repository for our customers. I have rewritten the ASP side of things and cleaned that up but we have run across a problem. The internal (3rd party) software when sending files to the repository will either send an INSERT or an UPDATE statement dependent on an unknown (by me or any of the other employees here) setting. I could play with all of the settings and hope I find the one that turns this off. (There are no obvious candidates). What I'd rather do is create a trigger that will, on insert, check to see if there are already records that have the same filename and job ID and update the records instead of creating a new one.

[sknake]

That sounds like a good idea. Did you have a question or just wanted another opinion on it? I haven't tried to use a trigger like that before and I don't know if the trigger will fire if the SQL Server detects that it is about to violate a UNIQUE or PRIMARY KEY constraint. That would be something you should test first.

[nolesce]

Actually I wanted to avoid reinventing the wheel. I have made some progress toward this trigger and I believe I will be able to do it on my own. As for the constraints this wouldn't effect those. The filename being unique isn't required because the 3rd party software renames the file with the primary key of the new record and the original file extension (ie ThisDoc.txt becomes 12345.txt ) then safes the original filename in the record and renames it to the original name when downloaded. Our internal file naming convention makes this unnecessary. The larger problem is that the duplicate records didn't actually get the new file so our end users are faced with an excessive number of links a few of which actually work (the non-working links download 0kb sized files adding to the confusion). So better to prevent future dupes and then clean up existing ones. I'll be checking the filename and jobid for duplicated entries and denying new inserts that match those criteria. I know I said we never have duplicate file names but saying something 'never' happens really means I think it never happens or it hasn't happened yet. I'll up date things here and post the trigger once I have it working since I found precious little on this type of thing on the web.

[nolesce]

Here is the actual trigger. A caveat I use the 'replace' function:

REPLACE(FileName,' ','_')

because there is another trigger on the table that replaces any spaces in the file name with underscores. (MSIE was choking on the spaces in the download link).

CREATE TRIGGER [dbo].[trg_Update_existing_files]
   ON [dbo].[FileInfo] FOR INSERT
AS

IF EXISTS(SELECT REPLACE(I.FileName,' ','_'),I.JobID 
		From Inserted I, FileInfo F
		Where  REPLACE(I.FileName,' ','_') = F.FileName AND I.JobID = F.JobID
		Group By I.FileName,I.JobID
		Having COUNT(*) >1)
BEGIN
	RAISERROR('>>>DUPLICATES PREVENTED<<< ',10,1)
	ROLLBACK TRAN
END

I also made that garish error message because the third party software echoes the raw SQL error to the user in a pop up and by grabbing their attention I can reduce the panic level.