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
|
Trigger Definition
