set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER TRIGGER [updatecpxxqhsl] ON [dbo].[cpxx]
FOR INSERT, UPDATE, DELETE
AS
beginif update(sl3)
declare
@id int,
@sl3 float set @id=(select id from inserted) --获取被修改行ID
set @sl3=(select sl3 from inserted) --获取修改后内容begin
if (select sl3 from inserted) > 0
begin
update cpxx set qhsl002 = sl3 where id = @id
end
if (select sl3 from inserted) <= 0
begin
update cpxx set qhsl002 = sl3 where id = @id
end
end
end如果 update cpxx set sl3='1000' where id='22' 更新一条数据 可以成功如果 update cpxx set sl3='1000' 更新多条数据 报错了消息 512,级别 16,状态 1,过程 updatecpxxqhsl,第 11 行
子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
语句已终止。
请教下,我该怎么改?
FOR INSERT, UPDATE, DELETE
AS
beginif update(sl3)
update a set qhsl002 = i.sl3
from cpxx a,inserted i
where a.id = i.id
end
update a set sl3='1000' from cpxx c join inserted i on c.id=i.id
ALTER TRIGGER [updatecpxxqhsl] ON [dbo].[cpxx]
FOR INSERT, UPDATE, DELETE
AS
begin--加上這段
if NOT update(sl3)
RETURN
declare
@id int,
@sl3 float set @id=(select id from inserted) --获取被修改行ID
set @sl3=(select sl3 from inserted) --获取修改后内容begin
if (select sl3 from inserted) > 0
begin
update cpxx set qhsl002 = sl3 where id = @id
end
if (select sl3 from inserted) <= 0
begin
update cpxx set qhsl002 = sl3 where id = @id
end
end
endIF NOT EXISTS(SELECT 1 FROM DELETED) --新增
UPDATE c SET qhsl002 = i.sl3 FROM cpxx AS c INNER JOIN INSERTED i ON c.ID=a.ID
ELSE IF NOT EXISTS(SELECT 1 FROM inserted)--刪除
DELETE c FROM cpxx AS c INNER JOIN INSERTED i ON c.ID=a.ID
--ELSE 再有就是更新
FOR INSERT, UPDATE, DELETE
AS
begin--加上這段
if NOT update(sl3)
RETURN
IF NOT EXISTS(SELECT 1 FROM DELETED) --新增
UPDATE c SET qhsl002 = i.sl3 FROM cpxx AS c INNER JOIN INSERTED i ON c.ID=a.ID
ELSE IF NOT EXISTS(SELECT 1 FROM inserted)--刪除
DELETE c FROM cpxx AS c INNER JOIN INSERTED i ON c.ID=a.ID
--ELSE 再有就是更新
if NOT update(sl3)
RETURNALTER TRIGGER [updatecpxxqhsl] ON [dbo].[cpxx]
FOR INSERT, UPDATE, DELETE
AS
begin--加上這段
if NOT update(sl3)
RETURN UPDATE c SET qhsl002 = i.sl3 FROM cpxx AS c INNER JOIN INSERTED i ON c.ID=a.ID