update b set 投料次数=投料次数+1,投料量合计=投料量合计+i.投料量 from inerted i where datediff(day,i.日期,b.日期)=0insert b select 日期,投料次数=1,投料量合计=投料量 from inserted i where not exists ( select 1 from b where datediff(day,i.日期,b.日期)=0 )
我用下面的触发器,一直不行(不能新建) CREATE TRIGGER [trgUpdateB] ON [dbo].[A] FOR INSERT AS begin declare @TempWeight float,@TempDate datetime set @TempWeight=(select [投料量] from inserted) set @TempDate=(select [日期] from inserted) if(select count(*) from B where 日期=@TempDate)>0 begin insert into B values(@TempDate,1,@TempWeight) end else begin update B set [投料量次数]=[投料量次数]+1,[投料量合计]=[投料量合计]+@TempWeight where 日期=@TempDate end end
我用下面的触发器,一直不行(不能新建) CREATE TRIGGER [trgUpdateB] ON [dbo].[A] FOR INSERT AS begin declare @TempWeight float,@TempDate datetime set @TempWeight=(select [投料量] from inserted) set @TempDate=(select [日期] from inserted) if(select count(*) from B where 日期=@TempDate)>0 begin insert into B values(@TempDate,1,@TempWeight) end else begin update B set [投料量次数]=[投料量次数]+1,[投料量合计]=[投料量合计]+@TempWeight where 日期=@TempDate end end
CREATE TRIGGER [TRIGGER NAME] ON [dbo].[A] FOR INSERT, UPDATE AS . . . DECLARE @intFlag varchar(1) SET @intFlag='0' SELECT @intFlag='1' FROM B WHERE convert(varchar(10),isnull(日期,'1900-01-01'),120)=convert(char(10),getdate(),120) if @intFlag='1' begin update ... ... end else begin ....Insert... end
CREATE TRIGGER 名1 ON a FOR INSERT AS update b set 投料次数=投料次数+1,投料量合计=tem.投料量 from inserted tem where tem.日期=b.日期 insert b (日期,投料次数,投料量合计) select 日期,1,投料量 from inserted where 日期 not in (select 日期 from b) go
忘了说明一下:不一定是当天的值,插入随便任意一天,现在是if(select count(*) from B where 日期=@TempDate)>0不行,有正确答案吗?
create trigger on A for insert as begin declare @Count int,@Sum int if exists(select * from b where datediff(D,日期,getdate())=0) begin select @Count=投料次数,@Sum=投料量合计 from B where datediff(D,日期,getdate())=0 update B set 投料次数=@count+1,投料量合计=@Sum+(select 投料量 from inserted) where datediff(D,日期,getdate())=0 end else begin insert into B(日期,投料次数,投料量合计) select 日期,1,投料量 from inserted end end
create Trigger T_AddTableB on TableA for Insert as if (select convert(varchar max(日期),120) from B)=convert(varchar,getdate(),120) begin Update TableB set ... ... end else begin insert TableB select .... .. endgo
set @TempWeight=(select [投料量] from inserted) set @TempDate=(select [日期] from inserted)-->select @TempWeight=[投料量] from inserted select @TempDate=[日期] from inserted不过你这么写,一次只能插入一条纪录,一次插入多条纪录有问题的。
to 8992026(8992026) :我的if条件为什么不行?
改成:if exists (select 1 from B where 日期=@TempDate) 试试有一个问题,你的日期字端的数据是不是有时间,如果有,那就应该用 if exists (select 1 from B where datediff(day,日期,@TempDate)=0)
不需要if你在update 时加 where insert 时也加 where 不就可以了。
B表的问题已经解决了。 现在有C表:日期 班号 投料次数 投料量合计, 需要根据A表中的日期和班号来更新C表中的次数和合计。我用 pengdali(大力 V2.0)的语句update可以,insert不行,我想是条件不对 if(select 班号 from inserted)='白班' update...... insert C (日期,班号,投料次数,投料量合计) select 日期,'白班',1,投料量 from inserted where 日期 not in (select 日期 from b) and 班号 not in (select 班号 from C) if(select 班号 from inserted)='中班' update...... insert C (日期,班号,投料次数,投料量合计) select 日期,'中班',1,投料量 from inserted where 日期 not in (select 日期 from b) and 班号 not in (select 班号 from C)
CREATE TRIGGER 名1 ON a FOR INSERT AS update b set 投料次数=投料次数+1,投料量合计=tem.投料量 from inserted tem where tem.日期=b.日期 insert b (日期,投料次数,投料量合计) select 日期,1,投料量 from inserted where 日期 not in (select 日期 from b where 日期 is not null) go
忘了not in的bug CREATE TRIGGER 名1 ON a FOR INSERT AS update b set 投料次数=投料次数+1,投料量合计=tem.投料量 from inserted tem where tem.日期=b.日期 insert b (日期,投料次数,投料量合计) select 日期,1,投料量 from inserted where 日期 not in (select 日期 from b where 日期 is not null) go
to pengdali(大力 V2.0) : 班号的条件怎么加呢?班号有三种:“白班”“中班”“夜班”
FOR INSERT AS update b set 投料次数=投料次数+1,投料量合计=tem.投料量 from inserted tem where tem.日期=b.日期 and tem.班号=b.班号 insert b (日期,班号,投料次数,投料量合计) select 日期,班号,1,投料量 from inserted where 日期 not in (select 日期 from b where 日期 is not null) go
使用两个 not in 就出问题了,怎么办?
insert B (日期,班号,投料量次数,投料量合计) select 日期,班号,1,投料量 from inserted where cast(日期 as char(10))+班号 not in (select cast(日期 as char(10))+班号 from B )
set 投料次数=投料次数+1,投料量合计=投料量合计+i.投料量
from inerted i
where datediff(day,i.日期,b.日期)=0insert b
select 日期,投料次数=1,投料量合计=投料量
from inserted i
where not exists (
select 1 from b
where datediff(day,i.日期,b.日期)=0
)
CREATE TRIGGER [trgUpdateB] ON [dbo].[A]
FOR INSERT
AS
begin
declare @TempWeight float,@TempDate datetime
set @TempWeight=(select [投料量] from inserted)
set @TempDate=(select [日期] from inserted)
if(select count(*) from B where 日期=@TempDate)>0
begin
insert into B values(@TempDate,1,@TempWeight)
end
else
begin
update B set [投料量次数]=[投料量次数]+1,[投料量合计]=[投料量合计]+@TempWeight where 日期=@TempDate
end
end
CREATE TRIGGER [trgUpdateB] ON [dbo].[A]
FOR INSERT
AS
begin
declare @TempWeight float,@TempDate datetime
set @TempWeight=(select [投料量] from inserted)
set @TempDate=(select [日期] from inserted)
if(select count(*) from B where 日期=@TempDate)>0
begin
insert into B values(@TempDate,1,@TempWeight)
end
else
begin
update B set [投料量次数]=[投料量次数]+1,[投料量合计]=[投料量合计]+@TempWeight where 日期=@TempDate
end
end
FOR INSERT, UPDATE
AS
.
.
.
DECLARE @intFlag varchar(1)
SET @intFlag='0'
SELECT @intFlag='1' FROM B WHERE convert(varchar(10),isnull(日期,'1900-01-01'),120)=convert(char(10),getdate(),120)
if @intFlag='1'
begin
update ...
...
end
else
begin
....Insert...
end
FOR INSERT
AS
update b set 投料次数=投料次数+1,投料量合计=tem.投料量 from inserted tem where tem.日期=b.日期
insert b (日期,投料次数,投料量合计) select 日期,1,投料量 from inserted where 日期 not in (select 日期 from b)
go
as
begin
declare @Count int,@Sum int
if exists(select * from b where datediff(D,日期,getdate())=0)
begin
select @Count=投料次数,@Sum=投料量合计 from B
where datediff(D,日期,getdate())=0
update B set 投料次数=@count+1,投料量合计=@Sum+(select 投料量 from inserted)
where datediff(D,日期,getdate())=0
end else
begin
insert into B(日期,投料次数,投料量合计) select 日期,1,投料量 from inserted
end
end
as
if (select convert(varchar max(日期),120) from B)=convert(varchar,getdate(),120)
begin
Update TableB set ...
...
end
else
begin
insert TableB select ....
..
endgo
set @TempDate=(select [日期] from inserted)-->select @TempWeight=[投料量] from inserted
select @TempDate=[日期] from inserted不过你这么写,一次只能插入一条纪录,一次插入多条纪录有问题的。
试试有一个问题,你的日期字端的数据是不是有时间,如果有,那就应该用
if exists (select 1 from B where datediff(day,日期,@TempDate)=0)
insert 时也加 where 不就可以了。
现在有C表:日期 班号 投料次数 投料量合计,
需要根据A表中的日期和班号来更新C表中的次数和合计。我用 pengdali(大力 V2.0)的语句update可以,insert不行,我想是条件不对
if(select 班号 from inserted)='白班'
update......
insert C (日期,班号,投料次数,投料量合计) select 日期,'白班',1,投料量 from inserted where 日期 not in (select 日期 from b) and 班号 not in (select 班号 from C)
if(select 班号 from inserted)='中班'
update......
insert C (日期,班号,投料次数,投料量合计) select 日期,'中班',1,投料量 from inserted where 日期 not in (select 日期 from b) and 班号 not in (select 班号 from C)
FOR INSERT
AS
update b set 投料次数=投料次数+1,投料量合计=tem.投料量 from inserted tem where tem.日期=b.日期
insert b (日期,投料次数,投料量合计) select 日期,1,投料量 from inserted where 日期 not in (select 日期 from b where 日期 is not null)
go
CREATE TRIGGER 名1 ON a
FOR INSERT
AS
update b set 投料次数=投料次数+1,投料量合计=tem.投料量 from inserted tem where tem.日期=b.日期
insert b (日期,投料次数,投料量合计) select 日期,1,投料量 from inserted where 日期 not in (select 日期 from b where 日期 is not null)
go
班号的条件怎么加呢?班号有三种:“白班”“中班”“夜班”
AS
update b set 投料次数=投料次数+1,投料量合计=tem.投料量 from inserted tem where tem.日期=b.日期 and tem.班号=b.班号
insert b (日期,班号,投料次数,投料量合计) select 日期,班号,1,投料量 from inserted where 日期 not in (select 日期 from b where 日期 is not null)
go