PersonActivity.sql

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

Create Table dbo.PersonActivity
    (
    PersonActivityId int Not Null Identity (1, 1),
    PersonId int Not Null,
    ActivityId int Not Null,
    Evaluator nvarchar(30) Not Null,
    DateOfActivity datetime Not Null,
    GradeId int Not Null,
    RawScore smallint Not Null Constraint DF_PersonActivity_RawScore Default (0),
    AdjScore smallint Not Null,
    Comment ntext Null
    )
Go
Alter Table dbo.PersonActivity Add Constraint
    PK_PersonActivity Primary Key Nonclustered
    (
    PersonActivityId
    )
Go
Alter Table dbo.PersonActivity Add Constraint
    FK_PersonActivity_Activity Foreign Key
    (
    ActivityId
    ) References dbo.Activity
    (
    ActivityId
    )
Go
Alter Table dbo.PersonActivity Add Constraint
    FK_PersonActivity_Grade Foreign Key
    (
    GradeId
    ) References dbo.Grade
    (
    GradeId
    )
Go
Alter Table dbo.PersonActivity Add Constraint
    FK_PersonActivity_Person Foreign Key
    (
    PersonId
    ) References dbo.Person
    (
    PersonId
    )
Go
CREATE TRIGGER lw_tr_activityStats
   ON dbo.PersonActivity
   FOR insert
AS
set nocount on
declare @activityId int
declare @average float
declare @stdev float
declare allrecs cursor read_only fast_forward
   for select distinct activityId from inserted
open allrecs
fetch from allrecs into @activityId
while @@FETCH_STATUS=0
begin
   -- update the activity scores
   select @average=avg(rawScore),@stdev=stdev(rawScore) from personActivity pa
         join grade g on pa.gradeId=g.gradeId
      where pa.activityId=@activityId and g.affectAverage=1
   update activity set average=@average,standardDeviation=@stdev
      where activityId=@activityId
   fetch next from allrecs into @activityId
end
close allrecs
deallocate allrecs

Go