[Footsie]

Using SQL Server 2008 Express I want to create a trigger on my one table (dbo.Post) that automatically updates another column in a different table (dbo.Place) with the average of the first column (dbo.Post) where the placeIDs on both tables match.

This is what I have so far:
I know I'm using an INSERT here but can't work out how to use the UPDATE statement instead...

CREATE TRIGGER AvgRatingTrigger
ON dbo.Post
AFTER INSERT
AS
INSERT INTO dbo.Place (AvgRating) /*Possibly should be UPDATE?*/
SELECT AVG(Rating) FROM dbo.Post

I would think I need something like WHERE Post.PlaceID = Post.PlaceID after the SELECT statement above but have tried that and it doesn't work.
If I do add that in I get this error: "The multi-part identifier "dbo.Post.Rating" could not be bound."

These are the tables:

Post table:

PostID - PK - uniqueIdentifier
UserID - FK - uniqueIdentifier
PlaceID - FK - int
Review - text
Rating - int
ReviewTime - datetime

Place table:

PlaceID - PK - int
CategoryID - FK - int
Country - varchar
City - varchar
Place - varchar
AvgRating - int

[June7]

Why do you need to save calcuated value? Basic principle of relational database is to save raw data and perform calcs on reports.

Check this link (one of many) for guidance on SQL statement syntax:
http://www.w3schools.../sql_syntax.asp

[Footsie]

Thanks for your reply, but can you confirm?
What you are saying is that I should drop the AvgRating column altogether - and then when I need the Avg of the Rating column just return it as the result of a SELECT statement? Is that right?

That would make sense... hmmm...then every time the SELECT is called it would re-calculate the AVG of that column and the values would be up-to-date.
I think I was just getting lost in all the details.

Thanks I am aware of w3schools - I use it often.

This post has been edited by Footsie: 02 December 2009 - 12:57 AM

[June7]

Yes, I am saying you should reconsider the need to save the calculated value. There could be some compelling reason I would not be aware of, but as a norm, calculated results would not be saved. Especially simple calcs such as an average. That said, I myself have violated this tenent because of the special situation of one of my databases where calcs are very complex.