--sql毕竟不是vfp数据库,它没有记录号,也没有记录顺序的概念 --所以与上一条数据进行比较的说法本来就有问题--如果是变通一下,只要插入的数据与表中现有的数据的time1或time2相差>=30分钟就插入 --可以这样写处理的触发器 create trigger tr_insert on 表名 instead of insert as insert 表名(card,time1,time2,state) select card,time1,time2 ,state=case when time1>time2 then N'出' else N'入' end from inserted i where not exists( select * from 表名 where card=a.card and datediff(minute,time1,a.time1)<30 and datediff(minute,time1,a.time1)<30)
--写错了,应该是icreate trigger tr_insert on 表名 instead of insert as insert 表名(card,time1,time2,state) select card,time1,time2 ,state=case when time1>time2 then N'出' else N'入' end from inserted i where not exists( select * from 表名 where card=a.card and datediff(minute,time1,i.time1)<30 and datediff(minute,time1,i.time1)<30
card int,
time1 datetime,
time2 datetime,
state as case when time1>time2 then N'出' else N'入' end
)
card int,
time1 datetime,
time2 datetime,
state as case when time1>time2 then N'出' else N'入' end
)
行级别,所以应用起来比较麻烦。
原理:使用Update After触发器,
用游标遍历表中所有数据,对每一条记录进行如下判别
1、判断time1是否大于time2 如果满足条件用系统时间和time1比较,查看
是否已经超过30分钟,如果没有超过30分钟,就抛出异常,中断退出
2、判断time2是否大于time1 如果满足条件用系统时间和time2比较,查看
是否已经超过30分钟,如果没有超过30分钟,就抛出异常,中断退出
--所以与上一条数据进行比较的说法本来就有问题--如果是变通一下,只要插入的数据与表中现有的数据的time1或time2相差>=30分钟就插入
--可以这样写处理的触发器
create trigger tr_insert on 表名
instead of insert
as
insert 表名(card,time1,time2,state)
select card,time1,time2
,state=case when time1>time2 then N'出' else N'入' end
from inserted i
where not exists(
select * from 表名
where card=a.card
and datediff(minute,time1,a.time1)<30
and datediff(minute,time1,a.time1)<30)
instead of insert
as
insert 表名(card,time1,time2,state)
select card,time1,time2
,state=case when time1>time2 then N'出' else N'入' end
from inserted i
where not exists(
select * from 表名
where card=a.card
and datediff(minute,time1,i.time1)<30
and datediff(minute,time1,i.time1)<30