时间戳

首页 > 解决方案 > 使用触发器插入后出现错误“消息 512，级别 16”

sql - 使用触发器插入后出现错误“消息 512，级别 16”

问题描述

我需要创建一个触发器来Total在每次增加新的损坏后更新列。

Total 必须随着这种损坏的成本而增加，所以我有这个错误：

消息 512，级别 16，状态 1，过程 TotalDmg，第 4 行
子查询返回超过 1 个值。当子查询跟随 =、!=、<、<=、>、>= 或子查询用作表达式时，这是不允许的。

我的脚本：

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

标签： sqlsql-serverdatabasetsqltriggers

解决方案

Brent Ozar 说：我在大多数触发器中发现的沉默虫：

您没有考虑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

另请参阅我的帖子，以获得涉及索引视图的更好解决方案

推荐阅读