CODEWITHSUNDEEP
c#.netsql-servervisual-studio
Kay
Kay

Reputation: 3

SQL Server incrementing a column's value depending on a trigger from another table

I'm working on a WebApp with the following tables in it's database:

CREATE TABLE Store
(
   [Name_of_Store], [Number_of_Ratings_Received] TINYINT, [Average_Rating] TINYINT, 
 )
; 
 CREATE TABLE Employee
(
   [Name_of_Employee] ,[Number_of_Ratings_Received] TINYINT, [Average_Rating] TINYINT, 
 ) 
; 
 CREATE TABLE Rating 
 (

    [Rating_for_Employee_out_of_Ten] TINYINT, [Rating_for_Store_out_of_Ten] TINYINT,
)
;

For arguments sake, the tables contain the following data:

INSERT INTO Store
    ([Name_of_Store], [Average_Rating], [Number_of_Ratings_Received])
VALUES
    ('Med', '5', '1'),
    ('Chem', '4', '3'),
;

INSERT INTO Employee
    ([Name_of_Employee], [Average_Rating], [Number_of_Ratings_Received])
VALUES
    ('John', '5', '1'),
    ('Stephen', '1', '8'),
;

Assuming there are primary and foreign keys that link the tables accordingly. The Webapp updates the Rating table, but then I need the Rating table (using it's corresponding foreign keys to the Store and Employee table's primary keys) to trigger the ratings fields in the Store and Employee tables. For example, every time a 'Employee' rates a Store, I need the value contained in the 'Number_of_Ratings_Received' field of that particular Store to increase by 1 and the 'Average_Rating' field to adjust accordingly.

Bear in mind that this is my first attempt after watching tutorial videos. I just can't get the syntax right. So far I have:

GO
CREATE TRIGGER NumberOfRatingsReceived1 AFTER INSERT ON Store
    BEGIN
        UPDATE Store SET Number_of_Ratings_Received = AUTO_INCREMENT
    END

GO
    CREATE TRIGGER NumberOfRatingsReceived2 AFTER INSERT ON Employee
    BEGIN
        UPDATE Employee SET Number_of_Ratings_Received = AUTO_INCREMENT
    END

I'm struggling with getting the auto increment working, let alone the average calc. Please assist or point me into the right direction.

Upvotes: 0

Views: 50

Answers (2)

Hogan
Hogan

Reputation: 70538

I would suggest not storing the number of ratings and average. Instead create a view like the following to calculate the information on the fly and not have data duplication built into your model.

CREATE VIEW StoreWithStatistics AS
  SELECT s.*, 
         COUNT(r.StoreRating) OVER (PARTITION BY s.StoreID) AS Number_of_Ratings_Recieved,  
         AVG(r.Rating_for_Store_out_of_Ten) OVER (PARTITION BY s.StoreID) AS Average_Rating
  FROM STORE s
  LEFT JOIN Rating r on s.StoreID = r.StoreID

Upvotes: 2

Rahul
Rahul

Reputation: 77896

There is no AUTO_INCREMENT in SQL Server. I believe you meant to say after insert on Rating table update store and Employee table. So change your trigger body like below

CREATE TRIGGER NumberOfRatingsReceived1 
AFTER INSERT ON Rating
    BEGIN
        UPDATE Store 
        SET Number_of_Ratings_Received = Number_of_Ratings_Received + 1
        WHERE <condition>;
    END

Upvotes: 1

Related Questions