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?
varchar
without length.Is Null
and have declared avarchar(1)
. I am not sure what you mean by "your trigger is written assuming that every single update ever will affect exactly one row" - how so?