改为 SET IDENTITY_INSERT TotalQty ON insert into TotalQty(字段列表) select (字段列表) from inserted 不要用*好就可以
create trigger TotalQty_Insert_Update on Entrance for insert as if(select count(1) from I ,TotalQty T where TotalQty.TotalName=Entrance.EntranceName and TotalQty.TotalType=Entrance.EntranceType and TotalQty.TotalColor=Entrance.Color )>0 begin update T set TotalAmount=T.totalAmount+I.Amount ,T.TotalToPrice=T.TotalToPrice+I.TotalPrice from TotalQty as T inner join inserted as i on T.TotalName=I.EntranceName and T.TotalType=I.EntranceType and T.TotalColor=I.Color end else begin SET IDENTITY_INSERT TotalQty ON insert into TotalQty(字段列表.........) select * from inserted SET IDENTITY_INSERT TotalQty OFF end
支持vivianfdlpw(): 解释一下如果你想显示的向标识列上添加数据的话,你就不能使用*,必须写上每一个字段的名字,才可以 SET IDENTITY_INSERT TotalQty ON insert into TotalQ(字段1,字段2.........) select * from inserted SET IDENTITY_INSERT TotalQty OFF 这样应该就可以啦!
create trigger TotalQty_Insert_Update on Entrance for insert as if exists ( select count(1) from I,TotalQty T where TotalQty.TotalName=Entrance.EntranceName and TotalQty.TotalType=Entrance.EntranceType and TotalQty.TotalColor=Entrance.Color ) begin update T set TotalAmount=T.totalAmount+I.Amount ,T.TotalToPrice=T.TotalToPrice+I.TotalPrice from TotalQty as T inner join inserted as i on T.TotalName=I.EntranceName and T.TotalType=I.EntranceType and T.TotalColor=I.Color end else begin SET IDENTITY_INSERT TotalQty ON insert into TotalQty(字段列表.........) select * from inserted SET IDENTITY_INSERT TotalQty OFF end
新手總是煩人的,不要怪我呀.根據 vivianfdlpw() 的指導更改了,但還是出現錯誤,暈,請大家指教.create trigger TotalQty_Insert_Update on Entrance for insert as if(select count(1) from I,TotalQty T where TotalQty.TotalName=Entrance.EntranceName and TotalQty.TotalType=Entrance.EntranceType and TotalQty.TotalColor=Entrance.Color)>0 begin update T set TotalAmount=T.totalAmount+I.Amount,T.TotalToPrice=T.TotalToPrice+I.TotalPrice from TotalQty as T inner join inserted as i on T.TotalName=I.EntranceName and T.TotalType=I.EntranceType and T.TotalColor=I.Color end else begin SET IDENTITY_INSERT TotalQty ON insert into TotalQty(TotalDateTime,TotalName,TotalAmount,TotalType,TotalSpecification,TotalColor,TotalPrice,TotalUnit,TotalToPrice,TotalNumber,TotalProvider,TotalMan) select * from inserted SET IDENTITY_INSERT TotalQty OFF end 服务器: 消息 213,级别 16,状态 1,过程 TotalQty_Insert_Update,行 16 插入错误: 列名或所提供值的数目与表定义不匹配。
SET IDENTITY_INSERT TotalQty ON
insert into TotalQty(字段列表) select (字段列表) from inserted 不要用*好就可以
on Entrance
for insert
as
if(select count(1)
from I
,TotalQty T
where TotalQty.TotalName=Entrance.EntranceName
and TotalQty.TotalType=Entrance.EntranceType
and TotalQty.TotalColor=Entrance.Color
)>0
begin
update T
set TotalAmount=T.totalAmount+I.Amount
,T.TotalToPrice=T.TotalToPrice+I.TotalPrice
from TotalQty as T
inner join inserted as i on T.TotalName=I.EntranceName
and T.TotalType=I.EntranceType
and T.TotalColor=I.Color
end
else
begin
SET IDENTITY_INSERT TotalQty ON
insert into TotalQty(字段列表.........)
select * from inserted
SET IDENTITY_INSERT TotalQty OFF
end
解释一下如果你想显示的向标识列上添加数据的话,你就不能使用*,必须写上每一个字段的名字,才可以
SET IDENTITY_INSERT TotalQty ON
insert into TotalQ(字段1,字段2.........)
select * from inserted
SET IDENTITY_INSERT TotalQty OFF
这样应该就可以啦!
on Entrance
for insert
as
if exists
(
select count(1)
from I,TotalQty T
where TotalQty.TotalName=Entrance.EntranceName
and TotalQty.TotalType=Entrance.EntranceType
and TotalQty.TotalColor=Entrance.Color
)
begin
update T
set TotalAmount=T.totalAmount+I.Amount
,T.TotalToPrice=T.TotalToPrice+I.TotalPrice
from TotalQty as T
inner join inserted as i on T.TotalName=I.EntranceName
and T.TotalType=I.EntranceType
and T.TotalColor=I.Color
end
else
begin
SET IDENTITY_INSERT TotalQty ON
insert into TotalQty(字段列表.........)
select * from inserted
SET IDENTITY_INSERT TotalQty OFF
end
on Entrance
for insert
as
if(select count(1) from I,TotalQty T
where TotalQty.TotalName=Entrance.EntranceName
and TotalQty.TotalType=Entrance.EntranceType
and TotalQty.TotalColor=Entrance.Color)>0
begin
update T set TotalAmount=T.totalAmount+I.Amount,T.TotalToPrice=T.TotalToPrice+I.TotalPrice from TotalQty as T
inner join inserted as i on T.TotalName=I.EntranceName and T.TotalType=I.EntranceType and T.TotalColor=I.Color
end
else
begin
SET IDENTITY_INSERT TotalQty ON
insert into TotalQty(TotalDateTime,TotalName,TotalAmount,TotalType,TotalSpecification,TotalColor,TotalPrice,TotalUnit,TotalToPrice,TotalNumber,TotalProvider,TotalMan)
select * from inserted
SET IDENTITY_INSERT TotalQty OFF
end
服务器: 消息 213,级别 16,状态 1,过程 TotalQty_Insert_Update,行 16
插入错误: 列名或所提供值的数目与表定义不匹配。
============>
你指定的字段列表和inserted中的字段定义或数目不对应