Group.sql

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

Create Table dbo.[Group]
    (
    GroupId int Not Null Identity (1, 1),
    LocationId int Null,
    Name nvarchar(50) Not Null,
    Description nvarchar(128) Null,
    BeginDate datetime Not Null,
    EndDate datetime Null,
    BeginTime datetime Not Null,
    EndTime datetime Null,
    GradeScaleId1 int Null,
    GradeScaleId2 int Null,
    Period tinyint Null,
    MaxSize tinyint Null Constraint DF_Group_MaxSize Default (50),
    Size tinyint Null Constraint DF_Group_Size Default (0),
    MeetingDays nvarchar(7) Null
    )
Go
Alter Table dbo.[Group] Add Constraint
    PK_Group Primary Key Nonclustered
    (
    GroupId
    )
Go
Alter Table dbo.[Group] Add Constraint
    FK_Group_GradeScale Foreign Key
    (
    GradeScaleId1
    ) References dbo.GradeScale
    (
    GradeScaleId
    )
Go
Alter Table dbo.[Group] Add Constraint
    FK_Group_GradeScale1 Foreign Key
    (
    GradeScaleId2
    ) References dbo.GradeScale
    (
    GradeScaleId
    )
Go
Alter Table dbo.[Group] Add Constraint
    FK_Group_Location Foreign Key
    (
    LocationId
    ) References dbo.Location
    (
    LocationId
    )
Go
Create Trigger lw_tr_checkSize
On dbo.[Group]
For  Insert,Update
As
declare @maxSize int
declare @maxOccupancy int
set nocount on
-- Location's MaxOccupancy dictates Group's MaxSize
select @maxSize = maxSize, @maxOccupancy = maxOccupancy 
  from inserted i join location l on i.locationId=l.locationId
if @maxSize > @maxOccupancy 
  begin
    rollback transaction
    raiserror('MaxSize exceeded MaxOccupancy',16,1) with nowait
  end

Go