TR_CLASSCATEGORY_IUD

CREATE trigger TR_CLASSCATEGORY_IUD on dbo.CLASSCATEGORY for insert, update, delete not for replication as
begin
    set nocount on;
    begin try
        if exists(
            select CLASS.ID
            from dbo.CLASS
            left outer join dbo.CLASSCATEGORY on CLASS.ID = CLASSCATEGORY.CLASSID
            where (CLASS.ID in (select CLASSID from INSERTED) or CLASS.ID in (select CLASSID from DELETED)) and (CLASS.USECATEGORYPERCENTAGES = 1)
            group by CLASS.ID
            having SUM(CLASSCATEGORY.TOTALGRADEPERCENTAGE) != 100
        )
            raiserror('BBERR_CLASSCATEGORY_PERCENTAGESNOTEQUALHUNDRED', 13,1);
        if exists(
            select CLASS.ID
            from dbo.CLASS
            left outer join dbo.CLASSCATEGORY on CLASS.ID = CLASSCATEGORY.CLASSID
            where (CLASS.ID in (select CLASSID from INSERTED) or CLASS.ID in (select CLASSID from DELETED)) and (CLASS.USECATEGORYPERCENTAGES = 0)
            and CLASSCATEGORY.TOTALGRADEPERCENTAGE <> 0
        )
            raiserror('BBERR_CLASSCATEGORY_PERCENTAGESNONZERO', 13,1);
        if exists(
            select CLASS.ID
            from dbo.CLASS
            left outer join dbo.CLASSCATEGORY on CLASS.ID = CLASSCATEGORY.CLASSID
            where (CLASS.ID in (select CLASSID from INSERTED) or CLASS.ID in (select CLASSID from DELETED)) and (CLASS.USECATEGORYPERCENTAGES = 0)
            and CLASSCATEGORY.AVERAGINGMETHODCODE = 2
        )
            raiserror('BBERR_CLASSCATEGORY_AVERAGINGNMETHODINVALID', 13,1);                
    end try
    begin catch
        exec dbo.USP_RAISE_ERROR;
    end catch
end