SQLTeam.com | Weblogs | Forums

How can I validate serial number using a trigger?

sql2012

#21

The sample code explicitly has you checking only the first value INSERTed/UPDATEd, so I'll go with that. At this point, instructors must want to keep the code simple and not have you be forced to deal with multi-row INSERTs/UPDATEs.

If you also need to verify the check digit, let me know. But that's quite a bit of code, so I'm not doing it unless it's absolutely necessay.

IF OBJECT_ID ('TriggerVIN', 'TR') IS NOT NULL
DROP TRIGGER TriggerVIN;
GO
CREATE TRIGGER TriggerVIN ON CRM_Customers
AFTER INSERT, UPDATE 
AS 
BEGIN
SET NOCOUNT ON;

DECLARE @aVIN varchar(30); --increase len to check for VIN being too long also
DECLARE @errors varchar(1024);

SELECT TOP (1) @aVIN = i.VIN
FROM inserted i

IF LEN(@aVIN) <> 17
BEGIN 
    SET @errors = ISNULL(@errors + '; ', '') + 'VIN''s length is invalid because it is not 17 bytes.'
END --IF

IF ISNULL(SUBSTRING(@aVIN, 10, 1), '') NOT LIKE '[123456789ABCDEFGHJKLMNPRSTVWXY]'
BEGIN
    SET @errors = ISNULL(@errors + '; ', '') + 'VIN has invalid year code value.'
END --IF

IF @errors > ''
BEGIN
    RAISERROR(@errors, 16, 1)
    ROLLBACK TRANSACTION --optional
END --IF

END -- trigger definition
GO

#22

Thank you @khtan, the cursor was part of the previous assignment.

I agree @jcelko, I wouldn't use it if it weren't as an assignment.

Thank you @ScottPletcher, would a loop be best if it was more than one row?

All have been such a great help, I'm really glad I found this group and joined. Hopefully someday I will be the one responding helping others. Thank you!


#23

[quote="ScottPletcher, post:19, topic:4146"]
I believe K would be used for either one. The years must recycle every ~30 years, since there are a limited number of chars to represent year. The other elements of the VIN insure that the entire VIN in unique, even though the year code is re-used.
[/quote]Exactly what I was referring to.