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 @vResult numeric(8,0);
declare @vYear numeric(8,0);
DECLARE @aVIN VARCHAR(17);
SELECT TOP 1 @aVIN = i.VIN FROM INSERTED i;
END -- trigger definition
GO
commit
What are the validation rules?
What do you want to happen if the validation fails? Rollback the transaction?
A trigger is called once per insert/update even if more than one row is affected in the insert/update. So your trigger should be written to account for that possibility. The way it is written now, it will work only for a single row insert/update.
There are two length equal 17 char, and check digit date on or after 1990.
Sorry, new to forum. Also for an error msg and roll back tranaction.
The body of the trigger should be something like shown below.
CREATE TRIGGER TriggerVIN ON CRM_Customers
AFTER INSERT, UPDATE
AS BEGIN
SET NOCOUNT ON;
IF EXISTS
(
SELECT * FROM INSERTED
WHERE
LEN(VIN) <> 17
-- and check digit date. What part of the VIN has the year?
)
BEGIN
RAISERROR('Attempt to insert/update invalid VINs', 16, 1);
ROLLBACK
END
END
I am not familiar with what a VIN should be, or which part of the VIN would contain the year, so I can't help you there.
The best way to ask a question in SQL Team (or any forum) is to post some sample data and if applicable corresponding sample out put. So in your case, posting some sample VINs that are valid and some that are invalid would help.
Its the 10th char,
L 1990
M 1991
N 1992
P 1993
R 1994
S 1995
T 1996
V 1997
W 1998
X 1999
Y 2000
1 2001
2 2002
3 2003
4 2004
5 2005
6 2006
7 2007
8 2008
9 2009
A 2010
B 2011
C 2012
D 2013
E 2014
F 2015
G 2016
H 2017
J 2018
K 2019
L 2020
M 2021
N 2022
P 2023
R 2024
S 2025
T 2026
V 2027
W 2028
X 2029
Y 2030
1 2031
2 2032
3 2033
4 2034
5 2035
6 2036
7 2037
8 2038
9 2039
Why not use a check constraint on the table instead?
Class assignment and the instructor hasn't been very helpful in providing examples or instruction how to complete the task. From what I found online a constraint would be simpler.
VIN YrCD
1M8GDM9AXKP042788 K
JTHBA30GX40019020 4
LJCPCBLCX11000237 1
1M8GDM9A_KP042788 K
KLATF08Y1VB363636 V
WP0ZZZ99ZTS392124 T
1B7HF16Y7SS244322 S
1C3CDFAA1ED677123 E
You say that the length must be 17 but none of your sample data has that length. You say that the tenth character has the year but none of your sample data have a tenth character. You need to first gather the requirements; in this case define what a valid VIN would be. Also, is a trigger a requirement for this assignment or are you presupposing that a trigger is the answer? My first reaction was, as gbritton has stated, to use a constraint but perhaps the logic necessary would be more than a constraint would accommodate.
The space and the last digit are not part of the VIN, it was a query that ID the 10th digit.
A trigger is the requirement. I received a D on last assignment because I did it as easiest/fastest instead of what was required.
Here is full assignment as given. I added the SAMPLE DATA at the bottom.
-- 3. Create a trigger on the insert or update of any VIN to not allow a
-- VIN of other than 17 characters or an invalid check digit.
-- Do not allow a VIN earlier than 1990
-- A template of a trigger is provided
-- Provide a unit test.
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 @vResult numeric(8,0);
declare @vYear numeric(8,0);
DECLARE @aVIN VARCHAR(17);
SELECT TOP 1 @aVIN = i.VIN FROM INSERTED i;
-- PUT IN YOUR CODE HERE
END -- trigger definition
GO
commit
SAMPLE DATA
-- (7 row(s) affected)
-- 1M8GDM9AXKP042788 1989
-- JTHBA30GX40019020 2004
-- LJCPCBLCX11000237 2001
-- 1M8GDM9A_KP042788 1989
-- KLATF08Y1VB363636 1997
-- WP0ZZZ99ZTS392124 1996
-- 1B7HF16Y7SS244322 1995
The template has a fundamental flaw, in that it is leading you towards code that will not work if a single insert/update statement inserts or updates more than one row. Last time around, you got a D for doing the better/more efficient faster solution. So perhaps you will get a D if you wrote the code to handle multi-row inserts as well. If that is the case, sorry to say, you are between a rock and a hard place.
Really, the teaching assistant who created the template perhaps should take the class himself/herself.
Assuming you want to go with the flawed template, your "PUT IN YOUR CODE HERE" should have something like this:
IF LEN(@aVIN) = 17 AND RIGHT(@aVin,4) LIKE '[0-9][0-9][0-9][0-9]'
BEGIN
IF ( CAST(RIGHT(@aVin,4),INT) >= 1990 )
RETURN;
END
ROLLBACK
RAISERROR('**ERROR**',16,1);Thank you djj55. Your # 1 is why I think he is requiring nothing older than 1990. Car mfg dictate the coding. # 3 just error msg and rollback transaction, the VIN should be corrected before entry
.
@JamesK thank you as well, I would think to expect more than 1 row at a time with an insert statement, update maybe one at a time.
Well, it did mention using CURSOR, so it is 1 row at a time
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.
TRIGGERs are bad idea. They are pure procedural code in a declarative language. If this is actually a VIN, then look up the regular expression for a VIN and put it in a CHECK() constraint. One websife is:
http://regexlib.com/REDetails.aspx?regexp_id=516&AspxAutoDetectCookieSupport=1