这个是我的 update语句“update Market_SellDetail set effective=0 where effective=2 and addby='xh001'”,里面可以修改多条数据,用到了 一个触发器,但是出现“子查询返回的值多于一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
语句已终止。”的错误,触发器语句是这样“
CREATE TRIGGER insertintobacktbl ON [dbo].[Market_SellDetail]
FOR UPDATE
AS
DECLARE @old int
declare @new int
set @old =(select servicetypeid from deleted)
set @new=(select servicetypeid from inserted)
if @old=@new -- 检查运营商字段是否更新
insert into et_selldetail_back select * from deleted ”大家帮我看看,怎样解决,谢谢了,急等
语句已终止。”的错误,触发器语句是这样“
CREATE TRIGGER insertintobacktbl ON [dbo].[Market_SellDetail]
FOR UPDATE
AS
DECLARE @old int
declare @new int
set @old =(select servicetypeid from deleted)
set @new=(select servicetypeid from inserted)
if @old=@new -- 检查运营商字段是否更新
insert into et_selldetail_back select * from deleted ”大家帮我看看,怎样解决,谢谢了,急等
解决方案 »
- sql server本地存储过程怎么上传到服务器?_
- win20003下sqlserver2008r2无法连接win2008sp1下的sqlserver2008r2非默认实例
- 网络备份数据库问题
- 大家用mdf的时候都写这些代码吗?
- 一个看不懂的触发器,清高手给与解释一下,谢谢了昂!
- 当一个项目完成测试后,其后台数据库移到另一台机器上有以下3种选择,请问各有什么利弊
- 能不能创建一个触发器,让它一定时间的去执行
- SQL2000向表格中传入数据的问题
- sqlserver 如何利用fn_dblog( )函数进行日志解析?
- 用ado如何向SQL的IMAGE字段中存入文件
- index默认创建问题
- select distinct
CREATE TRIGGER insertintobacktbl ON [dbo].[Market_SellDetail]
FOR UPDATE
AS insert into et_selldetail_back select * from deleted where not exists(select 1 from inserted where key=deleted.key and servicetypeid =deleted. servicetypeid)
--key 可能有多個 就是你表的主鍵
set @old =(select servicetypeid from deleted)
set @new=(select servicetypeid from inserted)
--改为:
CREATE TRIGGER insertintobacktbl ON [dbo].[Market_SellDetail] FOR UPDATE
AS
DECLARE @old int
declare @new int
insert into et_selldetail_back select a.* from deleted a,inserted b where a.servicetypeid=b.servicetypeid
--sql 2005
update Market_SellDetail set effective=0
output deleted.* into et_selldetail_back
where effective=2 and addby='xh001'
FOR UPDATE
AS
insert into et_selldetail_back select * from deleted D
where not exists(select 1 from inserted I where I.servicetypeid=D.servicetypeid )
--关键在于:deleted 与 inserted表中有多行记录,你只需要改以下形式,取第一行记录.就不会出错了,这里没有考虑你的业务逻辑.
set @old =(select top 1 servicetypeid from deleted)
set @new=(select top 1 servicetypeid from inserted)