时间戳

首页 > 解决方案 > 触发sql问题

sql - 触发sql问题

问题描述

需要运行一个触发器来更新我的客户表中的列。这个想法是，一旦更新了客户表，触发器就需要使用标量更新不同的列。标量由函数计算。我不知道问题出在哪里，但出了点问题。

create FUNCTION calculateVirtualCashForCustomer(@email varchar) 
RETURNS float
AS BEGIN
    DECLARE @virtualCashToAdd float
    select  @virtualCashToAdd= (B.Price*(cast(O.Quantity as float)))*0.1
    from CUSTOMERS AS C JOIN BOXES AS B ON C.Email = B.Email JOIN ORDERS AS O ON O.BoxID = B.BoxID  
    where C.Email = @email
    RETURN  @virtualCashToAdd
End



CREATE TRIGGER updateVirtualCash
    on CUSTOMERS
    AFTER UPDATE
    AS
    DECLARE @Mail varchar(50)
    begin
    update CUSTOMERS
    set [Virtual Cash] = [Virtual Cash] + (dbo.calculateVirtualCashForCustomer(@Mail))
    where  @Mail= (select Email from dbo.Top10byMoney)
end

标签： sqlfunctiontriggers

解决方案

我认为问题在于where @Mail= (select Email from dbo.Top10byMoney)触发器。您可以尝试添加top 1，select以便仅与选定的值进行比较

例子：

CREATE TRIGGER updateVirtualCash
    on CUSTOMERS
    AFTER UPDATE
    AS
    DECLARE @Mail varchar(50)
    SET @Mail= (select Email from dbo.Top10byMoney)
    begin
    update CUSTOMERS
    set [Virtual Cash] = [Virtual Cash] + (dbo.calculateVirtualCashForCustomer(@Mail))
    where  Email=@Mail
end

为了解决值空问题，您可以使用 ISNULL

set [Virtual Cash] = ISNULL([Virtual Cash],0) + ISNULL(dbo.calculateVirtualCashForCustomer(@Mail),0)

推荐阅读