PersonGroup.sql

If Exists (Select * From sysobjects Where name = N'PersonGroup' And user_name(uid) = N'dbo')
    Drop Table dbo.PersonGroup
Go

Create Table dbo.PersonGroup
    (
    PersonGroupId int Not Null Identity (1, 1),
    PersonId int Not Null,
    GroupId int Not Null,
    PersonType nvarchar(1) Not Null Constraint DF_PersonGroup_PersonType Default ('I'),
    GradeScaleId int Null
    )
Go
Alter Table dbo.PersonGroup Add Constraint
    PK_PersonGroup Primary Key Nonclustered
    (
    PersonGroupId
    )
Go
Alter Table dbo.PersonGroup Add Constraint
    FK_PersonGroup_Group Foreign Key
    (
    GroupId
    ) References dbo.[Group]
    (
    GroupId
    )
Go
Alter Table dbo.PersonGroup Add Constraint
    FK_PersonGroup_GradeScale Foreign Key
    (
    GradeScaleId
    ) References dbo.GradeScale
    (
    GradeScaleId
    )
Go
Alter Table dbo.PersonGroup Add Constraint
    FK_PersonGroup_Person Foreign Key
    (
    PersonId
    ) References dbo.Person
    (
    PersonId
    )
Go
CREATE TRIGGER lw_tr_classSize
   ON dbo.PersonGroup
   FOR delete,insert
AS
declare @groupId int
declare @count int
-- subtract deleted records from class size
declare delrecs cursor read_only fast_forward
   for select groupId,count(*) from deleted where personType='I' group by groupId
open delrecs
fetch from delrecs into @groupId,@count
while @@FETCH_STATUS=0
begin
   update [group] set [size]=isnull([size]-@count,0) where groupId=@groupId
   fetch next from delrecs into @groupId,@count
end
close delrecs
deallocate delrecs
-- add new records to class size
declare addrecs cursor read_only fast_forward
   for select groupId,count(*) from inserted where personType='I' group by groupId
open addrecs
fetch from addrecs into @groupId,@count
while @@FETCH_STATUS=0
begin
   update [group] set [size]=isnull([size]+@count,@count) where groupId=@groupId
   fetch next from addrecs into @groupId,@count
end
close addrecs
deallocate addrecs

Go