3 Replies - 2238 Views - Last Post: 02 December 2009 - 07:46 PM Rate Topic: -----

#1 Footsie  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 23
  • View blog
  • Posts: 370
  • Joined: 20-September 07

Trigger to Insert avg from one column into another

Post icon  Posted 01 December 2009 - 04:26 AM

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



Is This A Good Question/Topic? 0
  • +

Replies To: Trigger to Insert avg from one column into another

#2 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: Trigger to Insert avg from one column into another

Posted 01 December 2009 - 05:26 PM

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
Was This Post Helpful? 0
  • +
  • -

#3 Footsie  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 23
  • View blog
  • Posts: 370
  • Joined: 20-September 07

Re: Trigger to Insert avg from one column into another

Posted 02 December 2009 - 12:55 AM

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

Was This Post Helpful? 0
  • +
  • -

#5 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: Trigger to Insert avg from one column into another

Posted 02 December 2009 - 07:46 PM

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.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1