表daycheck:
ID dnotedate empusername cdcs ztcs
1 2003-10-20 AA 2 2
2 2003-10-29 AA 1 2
3 2003-11-1 BB 1 1
4 2003-11-1 CC 1 2
5 2003-11-20 AA 1 1
6 2003-12-1 BB 1 1
13 2003-12-3 CC 1 3
14 2003-12-4 CC 1 3
15 2003-12-5 CC 1 3表monthcheck:ID months mnotedate empusername cdcs ztcs
1 10 2003-10-31 AA 1 4
2 11 2003-11-30 AA 1 1
3 11 2003-11-30 BB 1 1
4 11 2003-11-30 CC 1 2
5 12 2003-12-30 BB 4 8
12 12 2003-12-30 CC 5 13
我遇到的问题时在daycheck表中增加一条记录时的一个触发问题:分两种情况:1:当增加一条形如empusername=AA记录insert into daycheck(dnotedate,empusername,cdcs,ztcs) values('2003-12-5','AA','1','3')因为在monthcheck表中,months=12的所有empusername中
没有AA这一条,所以我就必须在monthcheck中增加一条。表monthcheck就是下面的数据:
ID months mnotedate empusername cdcs ztcs
1 10 2003-10-31 AA 1 4
2 11 2003-11-30 AA 1 1
3 11 2003-11-30 BB 1 1
4 11 2003-11-30 CC 1 2
5 12 2003-12-30 BB 4 8
12 12 2003-12-30 CC 5 13
13 12 2003-12-30 AA 1 3
--增加的一条2:当增加一条形如empusername=BB记录,insert into daycheck(dnotedate,empusername,cdcs,ztcs) values('2003-12-5','BB','1','3')因为在monthcheck表中,months=12的所有empusername中
有B表monthcheck就是下面的数据:
ID months mnotedate empusername cdcs ztcs
1 10 2003-10-31 AA 1 4
2 11 2003-11-30 AA 1 1
3 11 2003-11-30 BB 1 1
4 11 2003-11-30 CC 1 2
5 12 2003-12-30 BB 5 11 ----注意这里cdcs和ztcs值与最开始的变化!
12 12 2003-12-30 CC 5 13上面monthcheck表中months值来源于daycheck表中月份我的触发器如下:CREATE trigger moncheck
on daycheck
for insert,update,delete
as
declare @nowmon int,@empname varchar(50),@cdcs int,@ztcs int
select @nowmon=month(dnotedate),@empname=empusername,@cdcs=cdcs,@ztcs=ztcs from inserted
if exists(select empusername=@empname from monthcheck where months=@nowmon)
begin
update monthcheck set cdcs=cdcs+@cdcs , ztcs=ztcs+@ztcs where months=@nowmon and empusername=@empname
end
else
begin
insert into monthcheck(months,empusername,cdcs,ztcs) values(@nowmon,@empname,@cdcs,@ztcs)
end 具体问题是:当我运行第2种情况时,触发器可以正确执行,但是我运行第1种情况,触发器没起作用
上面表显示有问题,所以再贴一次
ID dnotedate empusername cdcs ztcs
1 2003-10-20 AA 2 2
2 2003-10-29 AA 1 2
3 2003-11-1 BB 1 1
4 2003-11-1 CC 1 2
5 2003-11-20 AA 1 1
6 2003-12-1 BB 1 1
13 2003-12-3 CC 1 3
14 2003-12-4 CC 1 3
15 2003-12-5 CC 1 3表monthcheck:ID months mnotedate empusername cdcs ztcs
1 10 2003-10-31 AA 1 4
2 11 2003-11-30 AA 1 1
3 11 2003-11-30 BB 1 1
4 11 2003-11-30 CC 1 2
5 12 2003-12-30 BB 4 8
12 12 2003-12-30 CC 5 13
我遇到的问题时在daycheck表中增加一条记录时的一个触发问题:分两种情况:1:当增加一条形如empusername=AA记录insert into daycheck(dnotedate,empusername,cdcs,ztcs) values('2003-12-5','AA','1','3')因为在monthcheck表中,months=12的所有empusername中
没有AA这一条,所以我就必须在monthcheck中增加一条。表monthcheck就是下面的数据:
ID months mnotedate empusername cdcs ztcs
1 10 2003-10-31 AA 1 4
2 11 2003-11-30 AA 1 1
3 11 2003-11-30 BB 1 1
4 11 2003-11-30 CC 1 2
5 12 2003-12-30 BB 4 8
12 12 2003-12-30 CC 5 13
13 12 2003-12-30 AA 1 3
--增加的一条2:当增加一条形如empusername=BB记录,insert into daycheck(dnotedate,empusername,cdcs,ztcs) values('2003-12-5','BB','1','3')因为在monthcheck表中,months=12的所有empusername中
有B表monthcheck就是下面的数据:
ID months mnotedate empusername cdcs ztcs
1 10 2003-10-31 AA 1 4
2 11 2003-11-30 AA 1 1
3 11 2003-11-30 BB 1 1
4 11 2003-11-30 CC 1 2
5 12 2003-12-30 BB 5 11 ----注意这里cdcs和ztcs值与最开始的变化!
12 12 2003-12-30 CC 5 13上面monthcheck表中months值来源于daycheck表中月份我的触发器如下:CREATE trigger moncheck
on daycheck
for insert,update,delete
as
declare @nowmon int,@empname varchar(50),@cdcs int,@ztcs int
select @nowmon=month(dnotedate),@empname=empusername,@cdcs=cdcs,@ztcs=ztcs from inserted
if exists(select empusername=@empname from monthcheck where months=@nowmon)
begin
update monthcheck set cdcs=cdcs+@cdcs , ztcs=ztcs+@ztcs where months=@nowmon and empusername=@empname
end
else
begin
insert into monthcheck(months,empusername,cdcs,ztcs) values(@nowmon,@empname,@cdcs,@ztcs)
end 具体问题是:当我运行第2种情况时,触发器可以正确执行,但是我运行第1种情况,触发器没起作用
上面表显示有问题,所以再贴一次
on daycheck
for insert,update,delete
as
update monthcheck set cdcs=cdcs+inserted.cdcs , ztcs=ztcs+inserted.ztcs
from monthcheck,inserted where monthcheck.months=month(dnotedate) and monthcheck.empusername=inserted.empusername
insert into monthcheck(months,empusername,cdcs,ztcs)
select month(a.dnotedate),a.empusername,a.cdcs,a.ztcs
from inserted a left join monthcheck b on b.months=month(a.dnotedate) and a.empusername=b.empusername
where b.empusername is null
insert into monthcheck(months,empusername,cdcs,ztcs) values(@nowmon,@empname,@cdcs,@ztcs)
这里的列不完全