我有這個資料庫，我創建了一個函式來更新學生的 GPA。
CREATE OR ALTER FUNCTION fGPA
(@Mno varchar(9))
RETURNS DECIMAL(10,4)
BEGIN
DECLARE @gpa decimal(10,4);
SET @gpa = (SELECT
(SUM(CASE e.Grade
WHEN 'A' THEN 4.0
WHEN 'B' THEN 3.0
WHEN 'C' THEN 2.0
WHEN 'D' THEN 1.0
WHEN 'F' THEN 0.0
END * c.Credit) / SUM(c.Credit))
FROM Enroll AS e
INNER JOIN Section AS s ON s.Sid = e.Sid
INNER JOIN Course AS c ON c.Cno = s.Cno
WHERE @Mno = e.Mno);
RETURN @gpa;
END
我現在正在嘗試創建一個觸發器，以便每當在 Enroll 中更新成績時，都會呼叫 fGPA 函式并更新 GPA。這是我所擁有的：
CREATE OR ALTER TRIGGER update_enroll
ON Enroll
AFTER UPDATE
AS
IF (UPDATE(Grade))
BEGIN
DECLARE @updateGPA DECIMAL(10,4);
SET @updateGPA = (SELECT dbo.fGPA(e.Mno)
FROM Enroll as e
INNER JOIN inserted i ON i.Mno = e.Mno);
UPDATE Student
SET student.GPA = @updateGPA;
END
我得到的錯誤是：
子查詢回傳了 1 個以上的值。當子查詢跟隨 =、!=、<、<=、>、>= 或當子查詢用作運算式時，這是不允許的。
當我嘗試執行此陳述句時會發生這種情況
UPDATE ENROLL
SET Grade = 'B'
WHERE Sid = '17504';
uj5u.com熱心網友回復：
…
BEGIN
/*
DECLARE @updateGPA DECIMAL(10,4);
SET @updateGPA = (SELECT dbo.fGPA(e.Mno)
FROM Enroll as e
INNER JOIN inserted i ON i.Mno = e.Mno);
*/
UPDATE Student
SET GPA = dbo.fGPA(Mno)
WHERE Mno IN (SELECT i.Mno FROM inserted AS i)
END
…
uj5u.com熱心網友回復：
你正在一個非常常見的錯誤與觸發器：在inserted表中可以有多個（或零）行。因此，您應該將其作為聯合更新來執行。
請注意，該UPDATE()函式只告訴您該列是否存在于UPDATE陳述句中，而不是它是否實際更新過。您還應該加入deleted表格以排除此類行。
這導致了另一個常見問題：使用標量用戶定義函式。它們很慢，通常應該避免。相反，使用行內表值函式。
你也在更新整個Student表，你沒有加入任何東西。
CREATE OR ALTER FUNCTION fGPA
(@Mno varchar(9))
RETURNS TABLE
AS RETURN
SELECT
GPA = SUM(CASE e.Grade
WHEN 'A' THEN 4.0
WHEN 'B' THEN 3.0
WHEN 'C' THEN 2.0
WHEN 'D' THEN 1.0
WHEN 'F' THEN 0.0
END * c.Credit) / SUM(c.Credit)
FROM Enroll AS e
INNER JOIN Section AS s ON s.Sid = e.Sid
INNER JOIN Course AS c ON c.Cno = s.Cno
WHERE @Mno = e.Mno;
那你干脆CROSS APPLY吧。
CREATE OR ALTER TRIGGER update_enroll
ON Enroll
AFTER UPDATE
AS
SET NOCOUNT ON;
IF (NOT UPDATE(Grade) OR NOT EXISTS (SELECT 1 FROM inserted)) -- bail out for no rows
RETURN;
UPDATE s
SET s.GPA = gpa.GPA
FROM inserted i
JOIN Student s ON s.Mno = i.Mno
CROSS APPLY dbo.fGPA(i.Mno) gpa;
我會說我不認為觸發器真的是這個問題的正確答案。
理想情況下，您不應在兩個地方保留相同資訊的兩份副本。而是創建一個包含此計算的視圖。
CREATE VIEW vGPA
AS
SELECT
s.Mno,
GPA = SUM(CASE e.Grade
WHEN 'A' THEN 4.0
WHEN 'B' THEN 3.0
WHEN 'C' THEN 2.0
WHEN 'D' THEN 1.0
WHEN 'F' THEN 0.0
END * c.Credit) / SUM(c.Credit)
FROM dbo.Student AS s
INNER JOIN dbo.Enroll AS e ON e.Mno = s.Mno
INNER JOIN dbo.Section AS s ON s.Sid = e.Sid
INNER JOIN dbo.Course AS c ON c.Cno = s.Cno
GROUP BY
s.Mno;
如果你擔心性能，你可以索引視圖來支持它。服務器將確保索引始終與基表保持同步。
不幸的是，您不能在聚合（即SUM / SUM）上索引計算運算式。相反，創建一個包含兩個SUMs的基本視圖，然后創建另一個從中選擇值的標準視圖。
CREATE VIEW vGPA_indexed
WITH SCHEMABINDING -- needs schema binding
AS
SELECT
s.Mno,
SumGrade = SUM(CASE e.Grade
WHEN 'A' THEN 4.0
WHEN 'B' THEN 3.0
WHEN 'C' THEN 2.0
WHEN 'D' THEN 1.0
WHEN 'F' THEN 0.0
END * c.Credit),
SumCredit = SUM(c.Credit),
TotalRows = COUNT_BIG(*) -- needs count in grouped view
FROM dbo.Student AS s
INNER JOIN dbo.Enroll AS e ON e.Mno = s.Mno
INNER JOIN dbo.Section AS s ON s.Sid = e.Sid
INNER JOIN dbo.Course AS c ON c.Cno = s.Cno
GROUP BY
s.Mno;
CREATE UNIQUE CLUSTERED INDEX CX_vGPA_indexed on vGPA_indexed (Mno);
CREATE VIEW dbo.vGPA
AS
SELECT
s.Mno,
GPA = SumGrade / SumCredit
FROM dbo.vGPA_indexed WITH (NOEXPAND); -- needs NOEXPAND in standard edition, and anyway better for performance
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/369118.html
標籤：sql-server 触发器