Trigger to check for duplicates

[QueenKirsty]

Please can someone provide me with a solution to the following:

I have a table (Equipment) that holds details about items of equipment. The interesting columns of this table are:
EquipmentID (PK - integer)
MSerialNumber (nvarchar - allows Nulls).

I would like to have a trigger on the table so that when a record is added to the table or when the field MSerialNumber is updated it checks to see if that serial number already exists in the table. If it does I would like the update / insert to be cancelled and an error message displayed.

I have an additional issue that I would like to ignore all punctuation marks, spaces and preceding zeros in the MSerialNumber field when comparing them.
E.G.s
000000N124F6 matches N124F6
lp-240 matches lp240
00LPZ-R560 matches LPZR560

Commonly, the punctuation is only either spaces or hyphens.

All assistance gratefully appreciated.

 

[SQL_Hell]

Hi there

Why not put a unique constraint on column MSerialNumber?

 

[QueenKirsty]

Hi there

Why not put a unique constraint on column MSerialNumber?

Because I want to be able to recognise that 000123-6TH matches 1236TH and that AB 1234 matches AB1234. That sort of thing.

Any other suggestion?

 

[SQL_Hell]

It's going to be pretty complicated to do it this way and I don't think a trigger is going to be the best method.

Can the data not be controlled better from the application? or through the insert query that enters the data?

How is this data being entered?

 

[QueenKirsty]

I have now solved the problem with a function to strip the serial number and a trigger to do the check. I have attached the code for anyones interest in the future. Thanks for all assistance given!

FUNCTION

 Create function dbo.StripSerialNo (@MSerialNumber nvarchar(200))
RETURNS nvarchar(200) 
as begin declare 
[INDENT]@strNoHyphens varchar(200), 
@strNoSpaces varchar(200), 
@strNoZeros varchar(200) 
 
set @strNoHyphens = Replace(@MSerialNumber, '-', '') 
set @strNoSpaces = replace(@strNoHyphens, ' ', '') 
set @strNoZeros = replace(LTRIM(replace(@strNoSpaces, '0', ' ')), ' ', '0') 
return(@strNoZeros) 
[/INDENT]end

TRIGGER

CREATE trigger check_Serial_No 
on dbo.Equipment 
for Insert, update
as
declare 
[INDENT]@SNstripped  nvarchar(200),    
@SN nvarchar(200),    
@MID integer,    
@SerialNo nvarchar(200),    
@EID integer
[/INDENT]select @SN = MSerialNumber,  @MID = ManufacturerID,  @EID = EquipmentID
from inserted
 
if @SN is not null     
Begin    
[INDENT]set @SNstripped = dbo.StripSerialNo(@SN)    
if exists (select MSerialNumber, ManufacturerID from dbo.Equipment             where (dbo.StripSerialNo(MSerialNumber) = @SNstripped) and (ManufacturerID = @MID) and (EquipmentID <> @EID))       
begin       
[INDENT]raiserror ('An Equipment record already exists with that serial number. The record will nno be saved at this time.',16,1)       rollback transaction       
[/INDENT]end     
[/INDENT]END

 

[SQL_Hell]

I like the answer you've come up with here, good use of a trigger.
I am also impressed with the use of LTRIM and REPLACE to take care of the leading zeros. I would have done it a different way but your way is nicer I think

Good one