Location.sql

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

Create Table dbo.Location
    (
    LocationId int Not Null Identity (1, 1),
    Name nvarchar(50) Not Null,
    Street nvarchar(64) Null,
    City nvarchar(32) Null,
    Region nvarchar(32) Null,
    PostalCode nvarchar(20) Null,
    Country nvarchar(32) Null,
    Phone nvarchar(24) Null,
    Email nvarchar(128) Null,
    MaxOccupancy smallint Not Null Constraint DF_Location_MaxOccupancy Default (50),
    Comment ntext Null
    )
Go
Alter Table dbo.Location Add Constraint
    PK_Location Primary Key Nonclustered
    (
    LocationId
    )
Go
CREATE Trigger lw_tr_checkOccupancy 
On dbo.Location
For  Insert,Update
As
declare @maxOccupancy int
declare @locationId int
set nocount on
-- Location's MaxOccupancy dictates Group's MaxSize
declare lc cursor read_only fast_forward local
   for select locationId,maxOccupancy from inserted
open lc
fetch from lc into @locationId,@maxOccupancy
while @@FETCH_STATUS = 0
begin
   update [group] set maxSize = @maxOccupancy where locationId = @locationId And maxSize > @maxOccupancy
   fetch next from lc into @locationId,@maxOccupancy
end
close lc
deallocate lc

Go