问题描述:
有一个表,在将数据插入后将其中一个字段的值更新为该表中几个指定字段的值的组合.
以下是我写的触发器(添加数据的存储过程没问题-这个是可以肯定的)create TRIGGER [update_CarOrderInfo_afterInsert]
ON [dbo].[KS_U_CarOrderInfo]
AFTER insert
AS
declare @time varchar(200)
declare @timedesp varchar(200)
declare @timestr varchar(100)--用户自定义时间
declare @stime varchar(100)--准确时间
declare @unsuretime varchar(100)--指定时间段
select @stime=KS_StartTime from inserted
select @unsuretime=KS_UnSureTime from inserted
select @timedesp=KS_TimeDesp from inserted
set @timestr=cast(datepart(year,getdate()) as varchar)+cast(datepart(month,getdate()) as varchar)
set @timestr=@timestr+cast(datepart(day,getdate()) as varchar)+cast(datepart(hh,getdate()) as varchar)
set @timestr=@timestr+cast(datepart(mi,getdate()) as varchar)+cast(datepart(ss,getdate()) as varchar)
set @timestr=@timestr+cast(datepart(ms,getdate()) as varchar)
if(@stime !='' and @stime is not NULL)
BEGIN
set @time=@stime
update KS_U_CarOrderInfo
set Title='['+@time+']'+KS_StartCity+'('+KS_StartPoint+')至'+KS_DestionCity+'('+KS_DestionPoint+')',NewsID=@timestr
end
else if(@unsuretime!='' and @unsuretime is not NULL)
begin
set @time=@unsuretime
update KS_U_CarOrderInfo
set Title='['+@time+']'+KS_StartCity+'('+KS_StartPoint+')至'+KS_DestionCity+'('+KS_DestionPoint+')',NewsID=@timestr
end
else
begin
set @time=@timedesp
update KS_U_CarOrderInfo
set Title='['+@time+']'+KS_StartCity+'('+KS_StartPoint+')至'+KS_DestionCity+'('+KS_DestionPoint+')',NewsID=@timestr
end报的错误是:
子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。语句已终止。
有一个表,在将数据插入后将其中一个字段的值更新为该表中几个指定字段的值的组合.
以下是我写的触发器(添加数据的存储过程没问题-这个是可以肯定的)create TRIGGER [update_CarOrderInfo_afterInsert]
ON [dbo].[KS_U_CarOrderInfo]
AFTER insert
AS
declare @time varchar(200)
declare @timedesp varchar(200)
declare @timestr varchar(100)--用户自定义时间
declare @stime varchar(100)--准确时间
declare @unsuretime varchar(100)--指定时间段
select @stime=KS_StartTime from inserted
select @unsuretime=KS_UnSureTime from inserted
select @timedesp=KS_TimeDesp from inserted
set @timestr=cast(datepart(year,getdate()) as varchar)+cast(datepart(month,getdate()) as varchar)
set @timestr=@timestr+cast(datepart(day,getdate()) as varchar)+cast(datepart(hh,getdate()) as varchar)
set @timestr=@timestr+cast(datepart(mi,getdate()) as varchar)+cast(datepart(ss,getdate()) as varchar)
set @timestr=@timestr+cast(datepart(ms,getdate()) as varchar)
if(@stime !='' and @stime is not NULL)
BEGIN
set @time=@stime
update KS_U_CarOrderInfo
set Title='['+@time+']'+KS_StartCity+'('+KS_StartPoint+')至'+KS_DestionCity+'('+KS_DestionPoint+')',NewsID=@timestr
end
else if(@unsuretime!='' and @unsuretime is not NULL)
begin
set @time=@unsuretime
update KS_U_CarOrderInfo
set Title='['+@time+']'+KS_StartCity+'('+KS_StartPoint+')至'+KS_DestionCity+'('+KS_DestionPoint+')',NewsID=@timestr
end
else
begin
set @time=@timedesp
update KS_U_CarOrderInfo
set Title='['+@time+']'+KS_StartCity+'('+KS_StartPoint+')至'+KS_DestionCity+'('+KS_DestionPoint+')',NewsID=@timestr
end报的错误是:
子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。语句已终止。
ON [dbo].[KS_U_CarOrderInfo]
AFTER insert
AS
--但就你这个触发器看不出来问题。
--看看这个表有没有触发器在影响。。
--add********************************
set nocount on
declare @time varchar(200)
declare @timedesp varchar(200)
declare @timestr varchar(100)--用户自定义时间
declare @stime varchar(100)--准确时间
declare @unsuretime varchar(100)--指定时间段
select @stime=KS_StartTime from inserted
select @unsuretime=KS_UnSureTime from inserted
select @timedesp=KS_TimeDesp from inserted
set @timestr=cast(datepart(year,getdate()) as varchar)+cast(datepart(month,getdate()) as varchar)
set @timestr=@timestr+cast(datepart(day,getdate()) as varchar)+cast(datepart(hh,getdate()) as varchar)
set @timestr=@timestr+cast(datepart(mi,getdate()) as varchar)+cast(datepart(ss,getdate()) as varchar)
set @timestr=@timestr+cast(datepart(ms,getdate()) as varchar)
if(@stime !='' and @stime is not NULL)
BEGIN
set @time=@stime
update KS_U_CarOrderInfo
set Title='['+@time+']'+KS_StartCity+'('+KS_StartPoint+')至'+KS_DestionCity+'('+KS_DestionPoint+')',NewsID=@timestr
end
else if(@unsuretime!='' and @unsuretime is not NULL)
begin
set @time=@unsuretime
update KS_U_CarOrderInfo
set Title='['+@time+']'+KS_StartCity+'('+KS_StartPoint+')至'+KS_DestionCity+'('+KS_DestionPoint+')',NewsID=@timestr
end
else
begin
set @time=@timedesp
update KS_U_CarOrderInfo
set Title='['+@time+']'+KS_StartCity+'('+KS_StartPoint+')至'+KS_DestionCity+'('+KS_DestionPoint+')',NewsID=@timestr
end
--看看这个表:KS_U_CarOrderInfo有没有触发器在作怪。
select @unsuretime=KS_UnSureTime from inserted
select @timedesp=KS_TimeDesp from inserted update KS_U_CarOrderInfo
set Title='['+@time+']'+KS_StartCity+'('+KS_StartPoint+')至'+KS_DestionCity+'('+KS_DestionPoint+')',NewsID=@timestr后面加条件试试看
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(a int ,b int ,c int ,
d as isnull(a,0)+isnull(b,0)+isnull(c,0) --楼主 你建触发器太麻烦了 你建表的时候可以直接将该列作为计算列 希望可以给你灵感
)
go
insert into tb
select 1,2,3 union all
select 1,4,3 union all
select 1,2,3 union all
select 1,2,2 union all
select 1,8,3
go
select * from tb
/*------------
a b c d
----------- ----------- ----------- -----------
1 2 3 6
1 4 3 8
1 2 3 6
1 2 2 5
1 8 3 12(5 行受影响)
-------*/
子查询返回的值不止一个。当子查询跟随在 =、!=、 <、 <=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。语句已终止
我加了set nocount on这个,还是一样的.
set @timestr=@timestr+cast(datepart(day,getdate()) as varchar)+cast(datepart(hh,getdate()) as varchar)
set @timestr=@timestr+cast(datepart(mi,getdate()) as varchar)+cast(datepart(ss,getdate()) as varchar)
set @timestr=@timestr+cast(datepart(ms,getdate()) as varchar)
楼主这一段只是将当前时间转换成字符型,CONVERT(VARCHAR(20),GETDATE()
2、看看在这个表上是否还有其他的触发器
3、从语义上来说,这个触发器有一些疑问:
update KS_U_CarOrderInfo
set Title='['+@time+']'+KS_StartCity+'('+KS_StartPoint+')至'+KS_DestionCity+'('+KS_DestionPoint+')',NewsID=@timestr这样的话会修改整个表,觉得楼主的意思可能是要这样吧:
update KS_U_CarOrderInfo
set Title='['+@time+']'+KS_StartCity+'('+KS_StartPoint+')至'+KS_DestionCity+'('+KS_DestionPoint+')'
whereNewsID=@timestr
4、从功能上来说,貌似不用触发器就可以实现,又何必这么麻烦呢?
ON [dbo].[KS_U_CarOrderInfo]
AFTER insert
AS
update b set
Title='['+b.KS_StartTime+']'+b.KS_StartCity+'('+b.KS_StartPoint+')至'+b.KS_DestionCity+'('+b.KS_DestionPoint+')'
,NewsID=replace(replace(replace(replace(convert(varchar(23),getdate(),112),'-',''),' ',''),':',''),'.','')
from KS_U_CarOrderInfo b,inserted i
where b.主键=i.主键
Title字段可以用计算字段
NewsID字段设置默认 replace(replace(replace(replace(convert(varchar(23),getdate(),112),'-',''),' ',''),':',''),'.','') 就可以了
select @unsuretime=KS_UnSureTime from inserted
select @timedesp=KS_TimeDesp from inserted这三句处理不了多笔触发的情况的