Insert Trigger question

[Delphin]

I just found out that we are importing the same data multiple times throuought the day in a new table. Since I cannot query information inserted as it is coming from a text file, I need to write an insert trigger that will add the record if the IDNumber does not exist. If it does exist do not insert the record and continue. How would I do this?

I have so far:

CREATE TRIGGER Remover ON [dbo].[SMDR]
FOR INSERT
AS
Begin
Select IDNumber from inserted

(Now I need to know how to match it up in the table to find out if it already exists. If it does, do not insert, if it does not, insert it)

end

 

[vongrunt]

Hm... INSERTing rows to check whether column value is duplicate or not spells "abuse". It is better to import text file into intermediate SQL table (for temporary purposes), then INSERT INTO... SELECT only new records with classic LEFT JOIN trick. And for data integrity UNIQUE constraint (PK or index) on IDNumber is recommended.

Anyway, this should work assuming that text file itself has no duplicates:

create trigger Remover on SMDR
[b]instead of insert[/b]
as 
begin
	insert into SMDR (IDNumber, blah blah )
	select I.IDNumber, I.blah blah
	from inserted I 
	left join SMDR S on I.IDNumber=S.IDNumber
	where S.IDNumber is null
end