drop table k07 create table k07 ( id int identity(1,1), a0188 int , k0700 datetime, k0701 datetime, k0702 varchar(20), k0705 varchar(30), k0704 int ) alter trigger trigger_k07 on k07 after insert as declare @cout int;
if exists (select 1 from inserted) begin select @cout =count(1) from k07 where k0705='忘打卡' and a0188 in (select a0188 from inserted); if(@cout >3) begin update k07 set k0705='超过三次补刷卡就不能补刷卡' from k07 where id=( select id from inserted) end end insert into k07 select 20, cast('2010-12-31 00:00:00.000' as datetime), cast('1900-01-01 11:59:00.000' as datetime),'011','忘打卡', 0 union all select 20, cast('2010-12-30 00:00:00.000' as datetime), cast('1900-01-01 12:57:00.000' as datetime),'011','忘打卡', 0 union all select 20, cast('2010-12-29 00:00:00.000' as datetime), cast('1900-01-01 12:57:00.000' as datetime),'011','忘打卡', 0 union all select 20, cast('2010-12-29 00:00:00.000' as datetime), cast('1900-01-01 17:31:00.000' as datetime),'011','打卡', 1 select * From k07; insert into k07 select 20, cast('2010-12-31 00:00:00.000' as datetime), cast('1900-01-01 11:59:00.000' as datetime),'011','忘打卡', 0; select * From k07; ================================ id a0188 k0700 k0701 k0702 k0705 k0704 ----------- ----------- ----------------------- ----------------------- -------------------- ------------------------------ ----------- 5 20 2010-12-31 00:00:00.000 1900-01-01 11:59:00.000 011 忘打卡 0 6 20 2010-12-30 00:00:00.000 1900-01-01 12:57:00.000 011 忘打卡 0 7 20 2010-12-29 00:00:00.000 1900-01-01 12:57:00.000 011 忘打卡 0 8 20 2010-12-29 00:00:00.000 1900-01-01 17:31:00.000 011 打卡 1 9 20 2010-12-31 00:00:00.000 1900-01-01 11:59:00.000 011 超过三次补刷卡就不能补刷卡 0(5 行受影响)
为什么要用触发器?刷卡事件的时候直接count一下就行了吧? 为什么。。为什么
create trigger a on test1 after insert as
declare @count int; select @count=count(1) from test1 where k0705='忘打卡' and datediff(m,0,col)=datediff(m,o,getdate()); if(@count=3) begin print '您已经三次没有打卡了' end
create trigger a on k07 for insert,update as declare @count int; select @count=count(1) from k07,a01 where k0705 in('忘打卡') and datepart(mm,k0700)=datepart(mm,getdate()) if(@count=3) begin print '你本月补卡次数已达到3次!' end
a0188 k0700 k0701 k0702 k0705 k0704
20 2010-12-31 00:00:00.000 1900-01-01 11:59:00.000 011 忘打卡 0 20 2010-12-31 00:00:00.000 1900-01-01 12:57:00.000 011 忘打卡 0
20 2010-12-31 00:00:00.000 1900-01-01 17:31:00.000 011 忘打卡 0
[/Quote]
不是一个月底统计一次,就是某个人这个月如果累计有三次忘打卡记录,下一次补刷卡时就要有提示。。向k07
表插入、更新记录就要提示。。
统计这个人有多少次未刷卡,
然后通过程序提示他本月有N次未刷卡不就行了么?
select @i=count(*) from tb where datediff(month,0,col)=datediff(month,0,getdate())
and name='aaa' and col2='未打卡'
if @i>=3
--提示未刷卡。
根据得到的count次数提示。
逻辑就是这样了。不用触发器处理。
drop table k07
create table k07
(
id int identity(1,1),
a0188 int ,
k0700 datetime,
k0701 datetime,
k0702 varchar(20),
k0705 varchar(30),
k0704 int
)
alter trigger trigger_k07
on k07 after insert
as
declare @cout int;
if exists (select 1 from inserted)
begin
select @cout =count(1) from k07 where k0705='忘打卡' and a0188 in (select a0188 from inserted);
if(@cout >3)
begin
update k07 set k0705='超过三次补刷卡就不能补刷卡' from k07 where id=( select id from inserted)
end
end
insert into k07
select 20, cast('2010-12-31 00:00:00.000' as datetime), cast('1900-01-01 11:59:00.000' as datetime),'011','忘打卡', 0 union all
select 20, cast('2010-12-30 00:00:00.000' as datetime), cast('1900-01-01 12:57:00.000' as datetime),'011','忘打卡', 0 union all
select 20, cast('2010-12-29 00:00:00.000' as datetime), cast('1900-01-01 12:57:00.000' as datetime),'011','忘打卡', 0 union all
select 20, cast('2010-12-29 00:00:00.000' as datetime), cast('1900-01-01 17:31:00.000' as datetime),'011','打卡', 1 select * From k07;
insert into k07
select 20, cast('2010-12-31 00:00:00.000' as datetime), cast('1900-01-01 11:59:00.000' as datetime),'011','忘打卡', 0;
select * From k07;
================================
id a0188 k0700 k0701 k0702 k0705 k0704
----------- ----------- ----------------------- ----------------------- -------------------- ------------------------------ -----------
5 20 2010-12-31 00:00:00.000 1900-01-01 11:59:00.000 011 忘打卡 0
6 20 2010-12-30 00:00:00.000 1900-01-01 12:57:00.000 011 忘打卡 0
7 20 2010-12-29 00:00:00.000 1900-01-01 12:57:00.000 011 忘打卡 0
8 20 2010-12-29 00:00:00.000 1900-01-01 17:31:00.000 011 打卡 1
9 20 2010-12-31 00:00:00.000 1900-01-01 11:59:00.000 011 超过三次补刷卡就不能补刷卡 0(5 行受影响)
为什么。。为什么
on test1
after insert
as
declare @count int;
select @count=count(1) from test1 where k0705='忘打卡' and datediff(m,0,col)=datediff(m,o,getdate());
if(@count=3)
begin
print '您已经三次没有打卡了'
end
on k07
for insert,update
as
declare @count int;
select @count=count(1) from k07,a01 where k0705 in('忘打卡')
and datepart(mm,k0700)=datepart(mm,getdate())
if(@count=3)
begin
print '你本月补卡次数已达到3次!'
end