我需要创建一个触发器来在每次新增伤害后更新Total列。
总值必须随着伤害的增加而增加，所以我有这个错误:
Msg 512，级别16，状态1，过程TotalDmg，行4
子查询返回多个值。当子查询跟随=、!=、<、<=、>、>=或子查询用作表达式时，不允许这样做。
My script:
create database carsLoan
use carsLoan
create table Loan
(
idL int primary key,
Total float
)
insert into Loan
values (1, 300), (2, 200), (3, 400)
create table Damage
(
idD int primary key,
idL int references Loan,
description varchar(100),
Cost float
)
insert into Damage
values (1, 1, 'Damage1', 70), (2, 2, 'Damage2', 90), (3, 3, 'Damage3', 80)
alter trigger TotalDmg
on Damage
after insert
as
begin
declare @cost float
set @cost = (select Cost from Damage
where idD = (select idD from inserted))
declare @total float
set @total = (select Total from Loan
where idL = (select idL from inserted))
update Loan
set Total = @total + @cost
where idL = (select idL from inserted)
end
Brent Ozar说:我在大多数触发器中发现的沉默Bug:
您没有考虑到inserted有多行，甚至根本没有。
alter trigger TotalDmg on Damage after insert as
-- the whole batch is the trigger, no need for BEGIN/END
SET NOCOUNT, XACT_ABORT ON; -- best practice
IF (NOT EXISTS (SELECT 1 FROM inserted))
RETURN;
update l
set Total = l.Total + i.cost
from Loan l
join (
select i.idL, sum(i.Cost) cost
from inserted i
group by i.idL
) i on i.idL = l.idL;
GO
如果您创建了UPDATE触发器，请将inserted和deleted表与此代码进行比较:
join (
select i.idL, sum(i.Cost) cost
from (
select i.idL, i.Cost from inserted i
except
select d.idL, d.Cost from deleted d
) i
group by i.idL
) i on i.idL = l.idL