TR_PERFORMANCECATEGORYLEVELGRADE_INSERTUPDATE_VALIDGRADEORDER

CREATE trigger [dbo].[TR_PERFORMANCECATEGORYLEVELGRADE_INSERTUPDATE_VALIDGRADEORDER] on [dbo].[PERFORMANCECATEGORYLEVELGRADE]
after insert, update
as begin

set nocount on

    -- check that low/high grade are ordered correctly for specific translation table

    if exists(
        select i.ID 
        from inserted i
            join dbo.TRANSLATIONTABLEGRADE low      on low.TRANSLATIONTABLEID = i.TRANSLATIONTABLEID
                                                        and i.LOWESTVALUEGRADE = low.GRADE
            join dbo.TRANSLATIONTABLEGRADE high     on high.TRANSLATIONTABLEID = i.TRANSLATIONTABLEID
                                                        and i.HIGHESTVALUEGRADE = high.GRADE
        where 
            i.TRANSLATIONTABLEID is not null                    -- for specific translation table

            and i.GRADETYPECODE = 1
            and high.NUMERICEQUIVALENT < low.NUMERICEQUIVALENT)

        raiserror('BBERR_PERFORMANCECATEGORYLEVELGRADE_LOWGRADE_LESSTHANEQUAL_HIGHGRADE', 13, 1)


    -- check that low/high grade are ordered correctly for grades common across 'all' translation tables

    if exists(
        select i.ID
        from inserted i
            join dbo.TRANSLATIONTABLEGRADE low      on low.GRADE = i.LOWESTVALUEGRADE
            join dbo.TRANSLATIONTABLEGRADE high     on high.GRADE = i.HIGHESTVALUEGRADE
        where 
            i.TRANSLATIONTABLEID is null                    -- for 'all' translation tables

            and i.GRADETYPECODE = 1
        group by i.ID, low.GRADE, high.GRADE
        having max(high.NUMERICEQUIVALENT) < max(low.NUMERICEQUIVALENT))

        raiserror('BBERR_PERFORMANCECATEGORYLEVELGRADE_LOWGRADE_LESSTHANEQUAL_HIGHGRADE', 13, 1)

end