触发器（UPDATE后）导致错误？

I am using sql-server 2012 and i wrote a trigger like this:

我正在使用sql-server 2012，我写了一个这样的触发器：

ALTER TRIGGER [dbo].[ModValue] 
   ON  [dbo].[Table1]
   AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;
    declare @ocid bigint 
    declare @ncid bigint,@pid bigint

    set @ocid=(select CategoryId from deleted)
    select @ncid=CategoryId,@pid=Id from inserted

    if(@ocid<>@ncid)
    begin
    delete from [Table2] where ProductId=@pid
    delete from [Table3] where ProductId=@pid
    delete from [Table4] where ProductId=@pid
    end
END

When i want to update my table(Table1) i got this error:

当我想更新我的表（表1）时，我收到此错误：

Msg 512, Level 16, State 1, Procedure ModValue, Line 15
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

消息512，级别16，状态1，过程ModValue，行15子查询返回多个值。当子查询遵循=，！=，<，<=，>，> =或子查询用作表达式时，不允许这样做。该语句已终止。

Update query:

更新查询：

update Table1 
set sizing = 0 
where categoryid = 238

What is wrong with this scripts?

这个脚本有什么问题？

2 个解决方案

#1

I think the following line is the problem:

我认为以下问题是：

set @ocid=(select CategoryId from deleted)

SET command expects at most 1 line from the SELECT statement. deleted table may contain more than 1 line, as trigger will fire at batch level, not record level.

SET命令最多需要SELECT语句中的1行。删除的表可能包含多于1行，因为触发器将在批处理级别触发，而不是记录级别。

#2

The DELETED and INSERTED tables may contain more than one row.

DELETED和INSERTED表可能包含多行。

DELETED - contains data that existed before modifications (old)
DELETED - 包含修改前存在的数据（旧）
INSERTED - modified data (new).
INSERTED - 修改后的数据（新）。

So you have to find out where CategoryID was changed:

所以你必须找出CategoryID被改变的地方：

... 
from 
    Inserted new 
inner join 
    Deleted old on old.id = new.id 
where 
    new.CategoryID <> old.CategoryID

To do something with that while keeping in mind that there could be many rows, for example:

要做一些事情，同时记住可能有很多行，例如：

delete from [Table2] t2 
where exists (select 1 
              from Inserted new 
              inner join Deleted old on old.id = new.id 
              where new.CategoryID <> old.CategoryID 
                and t2.ProductId = d.ProductID)

Note that ProductID could be changed by UPDATE statement too.

请注意，也可以通过UPDATE语句更改ProductID。

#1