TR_PERFORMANCECATEGORYLEVELGRADE_INSERTUPDATE_GRADESVALIDFORTRANSLATIONTABLE

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

set nocount on

    -- check that low/high numeric grade within range of specific translation table

    if exists(
        select i.ID 
        from inserted i
        where 
            i.TRANSLATIONTABLEID is not null                    -- for specific translation table

            and i.GRADETYPECODE = 2
            and (i.LOWESTVALUENUMERIC < (select TRANSLATIONTABLE.LOWESTSCOREALLOWED from dbo.TRANSLATIONTABLE where TRANSLATIONTABLE.ID = i.TRANSLATIONTABLEID)
                or i.HIGHESTVALUENUMERIC > (select TRANSLATIONTABLE.HIGHESTSCOREALLOWED from dbo.TRANSLATIONTABLE where TRANSLATIONTABLE.ID = i.TRANSLATIONTABLEID)))

        raiserror('BBERR_PERFORMANCECATEGORYLEVELGRADE_NUMERICOUTSIDETRANSLATIONTABLERANGE', 13, 1)


    -- check that low/high numeric grade within range of 'all' translation tables

    if exists(
        select i.ID 
        from inserted i
        where 
            i.TRANSLATIONTABLEID is null                    -- for 'all' translation table

            and i.GRADETYPECODE = 2
            and (i.LOWESTVALUENUMERIC < (select max(TRANSLATIONTABLE.LOWESTSCOREALLOWED) from dbo.TRANSLATIONTABLE)
                or i.HIGHESTVALUENUMERIC > (select min(TRANSLATIONTABLE.HIGHESTSCOREALLOWED) from dbo.TRANSLATIONTABLE)))

        raiserror('BBERR_PERFORMANCECATEGORYLEVELGRADE_NUMERICOUTSIDETRANSLATIONTABLERANGE', 13, 1)


    -- check that low/high letter grade exist on specific translation table

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

            and i.GRADETYPECODE = 1
            and (low.ID is null or high.ID is null))

            raiserror('BBERR_PERFORMANCECATEGORYLEVELGRADE_GRADENOTINTRANSLATIONTABLE', 13, 1);

    -- check that low/high letter grade exist for across 'all translation tables'

    if exists(select i.ID from inserted i where i.TRANSLATIONTABLEID is null)
    begin

        declare @tt_count int = (select COUNT(ID) from dbo.TRANSLATIONTABLE);
        declare @grades table (GRADE nvarchar(10));

        -- get grades common across all translation tables

        insert into @grades
            select 
                GRADE
            from dbo.TRANSLATIONTABLEGRADE
            group by GRADE    
            having count(GRADE) = @tt_count    -- only if exists in all TRANSLATIONTABLES


        if exists(
            select i.ID 
            from inserted i
                left join @grades low on low.GRADE = i.LOWESTVALUEGRADE
                left join @grades high on high.GRADE = i.HIGHESTVALUEGRADE
            where 
                i.TRANSLATIONTABLEID is null                    -- for 'all' translation table

                and i.GRADETYPECODE = 1
                and (low.GRADE is null or high.GRADE is null))

            raiserror('BBERR_PERFORMANCECATEGORYLEVELGRADE_GRADENOTINALLTRANSLATIONTABLES', 13, 1)

    end
end