SQL Server根据来自另一个表的触发器增加列的值

本文关键字：触发器 增加 另一个 Server SQL | 更新日期: 2023-09-27 18:12:50

我正在研究一个WebApp，在它的数据库中有以下表:

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,
)
;

出于参数的考虑，表包含以下数据:

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'),
;

假设有主键和外键连接相应的表。Webapp更新Rating表，但随后我需要Rating表(使用与Store和Employee表的主键相对应的外键)来触发Store和Employee表中的评级字段。例如，每当"雇员"对商店进行评分时，我需要将该特定商店的"Number_of_Ratings_Received"字段中包含的值增加1，并相应地调整"Average_Rating"字段。

请记住，这是我看完教程视频后的第一次尝试。我就是搞不懂语法。到目前为止，我有:

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

我正在努力获得自动增量工作，更不用说平均calc.请协助或指出我正确的方向

SQL Server根据来自另一个表的触发器增加列的值

我建议不要存储评分和平均值的数量。相反，创建如下的视图来动态地计算信息，并且不要在模型中内置数据重复。

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

SQL Server中不存在AUTO_INCREMENT。我相信你的意思是在Rating表上插入后更新store和Employee表。所以像下面这样改变你的触发体

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