insert into Equipment (EquipmentID, MSerialNumber)
select @EquipmentID, @MSerialNumber
where  not exists (select * from Equipment x where x.EquipmentID = @EquipmentID)
and    not exists (select * from Equipment x where x.MSerialNumber = FN_CLEANUP_SERIALNO(@MSerialNumber))

Create function dbo.StripSerialNo (@MSerialNumber nvarchar(200))
RETURNS nvarchar(200)
as
begin
	declare @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)
end

CREATE trigger check_Serial_No 
on dbo.Equipment 
for Insert, update

as
declare @SNstripped  nvarchar(200),
	@SN nvarchar(200),
	@MID integer,
	@SerialNo nvarchar(200),
	@EID integer

select @SN = MSerialNumber,  @MID = ManufacturerID,  @EID = EquipmentID
from inserted

if @SN is not null
     Begin
	set @SNstripped = dbo.StripSerialNo(@SN)
	--if serial number exists already then
	if exists (select MSerialNumber, ManufacturerID 
			from dbo.Equipment 
			where (dbo.StripSerialNo(MSerialNumber) = @SNstripped) and (ManufacturerID = @MID) and (EquipmentID <> @EID))
	   begin
	   raiserror ('An Equipment record already exists with that serial number. 
			The record will nno be saved at this time.',16,1)
	   rollback transaction
	   end

     END