create trigger TR_STUDENTMARKINGCOLUMNGRADE_INSERTUPDATE_VALIDGRADES
on dbo.STUDENTMARKINGCOLUMNGRADE
after insert, update
not for replication
as begin
-- Check that StudentClassMeetingGroup and CourseGradingMarkingColumn IDs match up. If we are trying to insert a grade for
-- one class, but passing in CourseGradingID for a completely different course, that is not a valid grade.
-- This could happen thru the API, but shouldn't happen thru our UI.
if exists
(select *
from Inserted I
left join dbo.STUDENTCLASSMEETINGGROUP on I.STUDENTCLASSMEETINGGROUPID = STUDENTCLASSMEETINGGROUP.ID
left join dbo.CLASSMEETINGGROUP on CLASSMEETINGGROUP.ID = STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID
left join dbo.CLASS on CLASSMEETINGGROUP.CLASSID = CLASS.ID
left join dbo.V_COURSEGRADING as COURSEGRADING on CLASS.COURSEID = COURSEGRADING.COURSEID
left join dbo.COURSEGRADINGMARKINGCOLUMN on COURSEGRADING.ID = COURSEGRADINGMARKINGCOLUMN.COURSEGRADINGID
and I.COURSEGRADINGMARKINGCOLUMNID = COURSEGRADINGMARKINGCOLUMN.ID
where (CLASSMEETINGGROUP.STARTDATE >= COURSEGRADING.STARTDATE and CLASSMEETINGGROUP.ENDDATE <= COURSEGRADING.ENDDATE) and
(STUDENTCLASSMEETINGGROUP.ID is null or
COURSEGRADINGMARKINGCOLUMN.ID is null))
raiserror('BBERR_STUDENTMARKINGCOLUMNGRADE_UNRELATED_IDS', 13, 1)
-- GradeTypeCodes and ValuesAllowedCodes: 1 = Grade, 2 = Numeric
-- Check that Letter Grades are valid
if exists (
select *
from Inserted I
inner join dbo.COURSEGRADINGMARKINGCOLUMN on I.COURSEGRADINGMARKINGCOLUMNID = COURSEGRADINGMARKINGCOLUMN.ID
left join dbo.TRANSLATIONTABLEGRADE on COURSEGRADINGMARKINGCOLUMN.TRANSLATIONTABLEID = TRANSLATIONTABLEGRADE.TRANSLATIONTABLEID
and I.TRANSLATIONTABLEGRADEID = TRANSLATIONTABLEGRADE.ID
where
-- Blank grades are always valid
I.GRADEISBLANK = 0 and
-- And we are only checking Letter grades with this sql
I.GRADETYPECODE = 1 and
-- Invalid criteria. If we get a record back now, it is invalid.
-- (grade not in the translation table) OR (MC not graded) OR
-- (MC doesn't allow letter grades) OR (a Numeric Grade value is also specified)
(TRANSLATIONTABLEGRADE.ID is null or
COURSEGRADINGMARKINGCOLUMN.GRADED = 0 or
COURSEGRADINGMARKINGCOLUMN.VALUESALLOWEDCODE = 2 or
I.NUMERICGRADEVALUE is not null))
raiserror('BBERR_STUDENTMARKINGCOLUMNGRADE_INVALID_GRADE', 13, 1)
-- Check that Numeric Grades are valid
if exists (
select *
from Inserted I
inner join dbo.COURSEGRADINGMARKINGCOLUMN on I.COURSEGRADINGMARKINGCOLUMNID = COURSEGRADINGMARKINGCOLUMN.ID
left join dbo.TRANSLATIONTABLE on COURSEGRADINGMARKINGCOLUMN.TRANSLATIONTABLEID = TRANSLATIONTABLE.ID
where
-- Blank grades are always valid
I.GRADEISBLANK = 0 and
-- And we are only checking Numeric grades with this sql
I.GRADETYPECODE = 2 and
-- Invalid criteria. If we get a record back now, it is invalid.
-- (grade not in the translation table) OR (MC not graded) OR
-- (MC doesn't allow numeric grades) OR (a Letter Grade value is also specified)
(TRANSLATIONTABLE.ID is null or
not I.NUMERICGRADEVALUE between TRANSLATIONTABLE.LOWESTSCOREALLOWED and TRANSLATIONTABLE.HIGHESTSCOREALLOWED or
COURSEGRADINGMARKINGCOLUMN.GRADED = 0 or
COURSEGRADINGMARKINGCOLUMN.VALUESALLOWEDCODE = 1 or
I.TRANSLATIONTABLEGRADEID is not null))
raiserror('BBERR_STUDENTMARKINGCOLUMNGRADE_INVALID_GRADE', 13, 1)
end
|
Trigger Definition
