I have a form in a C# application that I can't change with a data table bound to a query. The user enters a mark for a student's task or 'A' for absent.
However, the person who designed this had the underlying table as all varchars, when really it would be nice to have decimals for marks and char for 'A'. To this end, I thought I would make an instead of update trigger that moves 'A' values to an absences table and puts marks in the existing marks table. I can then change the data type in my marks table to decimal.
This is my trigger code:
INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF (TRY_CONVERT(varchar,(SELECT weekReviewWk1 FROM inserted)) = '' OR (SELECT weekReviewWk1 FROM inserted) = NULL)
BEGIN
UPDATE assessmentData
SET assessmentData.weekReviewWk1 = NULL
FROM inserted
WHERE assessmentData.studentID = inserted.studentID
UPDATE assessmentAbsences
SET weekReviewWk1 = NULL
FROM inserted
WHERE assessmentAbsences.studentID = inserted.studentID
RETURN
END
IF (TRY_CONVERT(varchar,(SELECT weekReviewWk1 FROM inserted)) = 'A')
BEGIN
UPDATE assessmentAbsences
SET assessmentAbsences.weekReviewWk1 = inserted.weekReviewWk1
FROM inserted
WHERE assessmentAbsences.studentID = inserted.studentID
UPDATE assessmentData
SET weekReviewWk1 = NULL
FROM inserted
WHERE assessmentData.studentID = inserted.studentID
RETURN
END
BEGIN
UPDATE assessmentData
SET weekReviewWk1 = TRY_CONVERT(decimal(4,1), (SELECT weekReviewWk1 FROM inserted))
FROM inserted
WHERE assessmentData.studentID = inserted.studentID
UPDATE assessmentAbsences
SET weekReviewWk1 = NULL
FROM inserted
WHERE assessmentAbsences.studentID = inserted.studentID
END
END
The trigger works for decimal values, however I get "Error converting data type varchar to numeric" when I attempt to update the table with 'A'. The absences table has char as the type and the value should just be inserted into that, with nothing in the actual marks table, thus not violating the decimal type constraint. Why is this happening?
In addition, is there a better way to handle this scenario?
Best Answer
The presence of an
INSTEAD OF INSERT
trigger does not prevent SQL Server from validating the data-types of theINSERT
statement against the data-types of the target columns.The error message:
indicates that SQL Server knows it can't even try to insert 'A' into the numeric field. The trigger hasn't even ran at that point.
Having said that, you can use a trigger to put the absentee info into a another table, you just can't change the column type to a numeric value.
Here is an example:
The two
SELECT
statements above produce this output:Even though this does not allow you to actually change the data-type of the column, it does allow you to prevent entering incorrect data into that column. This will provide time for you to modify the code in the program to only enter numeric values into the
NumData
table, while preventing new "Absentee" rows being inserted into it. Once you get the code changed in the client program, you'dALTER
the column to benumeric
, and remove the trigger.The below code still fails since SQL Server cannot coalesce alphabetic values into numeric values:
This will work, which is likely how the client program is inserting rows: