TR_STUDENTMARKINGCOLUMNGRADE_INSERTUPDATE_VALIDGRADES

                    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