怎样用触发器、游标更新arrange记录

Question

有一个数据库表:brushCard，保存员工每天刷卡记录：empNo(员工编号),empName(员工名),DT(日期),bcTime(刷卡时间).
一个员工每天刷4次卡,早上上班,中午上班,晚上上班,晚上下班.
例如brushCard表数据:
empNo    empName        DT       bcTime
          005        张三     2010-1-1     7:45:42
          005        张三     2010-1-1     12:12:45
          005        张三     2010-1-1     17:45:42
          005        张三     2010-1-1     21:21:36
          006        李四     2010-1-1     7:25:20
          006        李四     2010-1-1     12:15:20
          006        李四     2010-1-1     17:45:20
          006        李四     2010-1-1     20:25:40
          007        王五     2010-1-1     7:04:00
          007        王五     2010-1-1     12:02:00
          007        王五     2010-1-1     17:34:16
          007        王五     2010-1-1     21:32:08早上上班刷卡时间范围是:7:00:00~9:00:00
中午上班刷卡时间范围是:12:00:00~14:00:00
晚上上班刷卡时间范围是:17:00:00~19:00:00
晚上下班刷卡时间范围是:20:00:00~22:00:00
问题：怎样利用触发器和游标?将brushCard数据更新到表arrange
arrange
empNo    empName        DT        bcTime1    bcTime2   bcTime3   bcTime4
          005       张三       2010-1-1      7:45:42   12:12:45  17:45:42  21:21:36
          006       李四       2010-1-1      7:25:20   12:15:20  17:45:20  20:25:40
          007       王五       2010-1-1      7:04:00   12:02:00  17:34:16  21:32:08      利用不同刷卡时间在不同时间段，将刷卡时间放在bcTime1，bcTime2，bcTime3，bcTime4中？

Answer 1

create trigger t 
on brushcard
after insert
as
begin
  insert into arrange(empNo, empName, DT)
  select distinct empNo, empName, DT
  from inserted a
  where not exists(select 1 
                   from arrange b
                   where a.empNo = b.empNo and a.DT = b.DT)
    
  update a
  set bcTime1 = b.bcTime, bcTime2 = c.bcTime, bcTime3 = d.bcTime, bcTime4 = e.bcTime 
  from arrange a
  full join inserted b
  on a.empNo = b.empNo and a.DT = b.DT and b.bcTime between '7:00:00' and '9:00:00' 
  full join inserted c
  on a.empNo = c.empNo and a.DT = c.DT and c.bcTime between '12:00:00' and '14:00:00' 
  full join inserted d
  on a.empNo = d.empNo and a.DT = d.DT and d.bcTime between '17:00:00' and '19:00:00' 
  full join inserted e
  on a.empNo = e.empNo and a.DT = e.DT and e.bcTime between '20:00:00' and '22:00:00' 
end

Answer 2

--> 测试数据：[tb]
if object_id('[brushCard]') is not null drop table [brushCard]
create table [brushCard]([empNo] varchar(3),[empName] varchar(4),[DT] varchar(10),[bcTime]  varchar(10))
insert [brushCard]
select '005','张三','2010-1-1' ,'7:45:42' union all
select '005','张三','2010-1-1' ,'12:12:45' union all
select '005','张三','2010-1-1', '17:45:42' union all
select '005','张三','2010-1-1', '21:21:36' union all
select '006','李四','2010-1-1' ,'7:25:20' union all
select '006','李四','2010-1-1' ,'12:15:20' union all
select '006','李四','2010-1-1' ,'17:45:20' union all
select '006','李四','2010-1-1', '20:25:40' union all
select '007','王五','2010-1-1', '7:04:00' union all
select '007','王五','2010-1-1', '12:02:00' union all
select '007','王五','2010-1-1', '17:34:16' union all
select '007','王五','2010-1-1', '21:32:08'select [empNo],[empName],[DT],
max(case when right('0'+[bcTime],8) between '07:00:00' and '09:00:00' then [bcTime] else '' end) as bcTime1,
max(case when right('0'+[bcTime],8) between '12:00:00' and '14:00:00' then [bcTime] else '' end) as bcTime2,
max(case when right('0'+[bcTime],8) between '17:00:00' and '19:00:00' then [bcTime] else '' end) as bcTime3,
max(case when right('0'+[bcTime],8) between '20:00:00' and '22:00:00' then [bcTime] else '' end) as bcTime4
from [brushCard]
group by [empNo],[empName],[DT]
--------------------------------
005 张三 2010-1-1 7:45:42 12:12:45 17:45:42 21:21:36
006 李四 2010-1-1 7:25:20 12:15:20 17:45:20 20:25:40
007 王五 2010-1-1 7:04:00 12:02:00 17:34:16 21:32:08

Answer 3

create trigger insertdata on brushCard
for insert
as
begin
declare @sql varchar(8000),@bcTime varchar(8),@empNo varchar(3)
select @bcTime=bcTime,@empNo=empNo from inserted
select @sql=case when right('0'+@bcTime,8) between '07:00:00' and '09:00:00' then 'bcTime1'
when right('0'+@bcTime,8) between '12:00:00' and '14:00:00' then 'bcTime2'
when right('0'+@bcTime,8) between '17:00:00' and '19:00:00' then 'bcTime3'
when right('0'+@bcTime,8) between '20:00:00' and '22:00:00' then 'bcTime4' end
select @sql = 'update arrange set '+@sql+' ='''+@bcTime+''' where empNo ='''+@empNo+''''
if exists(select 1 from arrange where empNo in (select empNo from inserted)) 
begin
exec(@sql)
end
else
begin
insert into arrange(empNo,empName,DT)
select empNo,empName,DT from inserted
exec(@sql)
end
end

Answer 4

--> 测试数据：[brushCard]
if object_id('[brushCard]') is not null drop table [brushCard]
create table [brushCard]([empNo] varchar(3),[empName] varchar(4),[DT] varchar(10),[bcTime]  varchar(10))
--> 测试数据：[arrange]
if object_id('[arrange]') is not null drop table [arrange]
create table [arrange]([empNo] varchar(3),[empName] varchar(4),[DT] varchar(10),
[bcTime1]  varchar(10),[bcTime2]  varchar(10),[bcTime3]  varchar(10),[bcTime4]  varchar(10))
--创建触发器
create trigger insertdata on brushCard
for insert
as
begin
declare @sql varchar(8000),@bcTime varchar(8),@empNo varchar(3)
select @bcTime=bcTime,@empNo=empNo from inserted
select @sql=case when right('0'+@bcTime,8) between '07:00:00' and '09:00:00' then 'bcTime1'
when right('0'+@bcTime,8) between '12:00:00' and '14:00:00' then 'bcTime2'
when right('0'+@bcTime,8) between '17:00:00' and '19:00:00' then 'bcTime3'
when right('0'+@bcTime,8) between '20:00:00' and '22:00:00' then 'bcTime4' end
select @sql = 'update arrange set '+@sql+' ='''+@bcTime+''' where empNo ='''+@empNo+''''
if exists(select 1 from arrange where empNo in (select empNo from inserted)) 
begin
exec(@sql)
end
else
begin
insert into arrange(empNo,empName,DT)
select empNo,empName,DT from inserted
exec(@sql)
end
end
--插入
insert [brushCard]
select '005','张三','2010-1-1' ,'7:45:42'
--结果
select * from arrange
---------------------------
005 张三 2010-1-1 7:45:42 NULL NULL NULL