CREATE TRIGGER [TRI_del] ON [TB]
FOR insert
AS
if (select count(a.*) from TB a,inserted b where a.deviceid=b.deviceid)>10
select min(A.datetime) as DateTime ,A.deviceid into #a from TB A, INSERTED B WHERE a.deviceid=b.deviceid
GROUP BY a.deviceid
delete TB from TB A,#A b where a.deviceid=b.deviceid and a.DateTime=b.DateTime
FOR insert
AS
if (select count(a.*) from TB a,inserted b where a.deviceid=b.deviceid)>10
select min(A.datetime) as DateTime ,A.deviceid into #a from TB A, INSERTED B WHERE a.deviceid=b.deviceid
GROUP BY a.deviceid
delete TB from TB A,#A b where a.deviceid=b.deviceid and a.DateTime=b.DateTime
for insert --邹建原来写成delete了,应该是笔误
as
if exists(
select 1 from 表 a,表 b,inserted i --加inserted表限制范围
where a.[datetime]<b.[datetime]
and datediff(hour,a.[datetime],b.[datetime])>6
and a.DeviceID=b.DeviceID
and a.DeviceID=i.DeviceID
)
delete a
from 表 a,insert i
where a.DeviceID=i.DeviceID
and (
select count(*) from 表
where DeviceID=a.DeviceID
and case
when [datetime]<'06:00'
then dateadd(day,1,[datetime])
else [datetime]
end
>
case
when a.[datetime]<'06:00'
then dateadd(day,1,a.[datetime])
else a.[datetime]
end)>=10
else
delete a
from 表 a,insert i
where a.DeviceID=i.DeviceID
and (select count(*) from 表 where DeviceID=a.DeviceID and [datetime]>a.[datetime])>=10
for insert
as
if (select count(1) from table a,inserted b where a.deviceid=b.deviceid)>9
begin
delete from table where DeviceID=inserted.DeviceID and DateTime in(select top 1 from table where DeviceID=inserted.DeviceID order by DateTime
end
for insert
as
if (select count(1) from table a,inserted b where a.deviceid=b.deviceid)>9
begin
delete from table where DeviceID=inserted.DeviceID and DateTime in(select top 1 from table where DeviceID=inserted.DeviceID order by DateTime)
end
min(DateTime)就是最小日期了
create trigger tr_insert on 表
for insert
as
delete a from 表 a
where exists(select 1 from inserted where DeviceID=a.DeviceID)
and(
select count(*) from 表
where DeviceID=a.DeviceID
and [DateTime]>=a.[DateTime]
)>10
CREATE TRIGGER [tr_del] ON 表
FOR insert
AS
if exists(select a.deviceid,count(*) as devicecount from 表 a,inserted b where a.deviceid=b.deviceid group by a.deviceid having count(*)>10)
delete 表
from 表 c,
(select min(a.datetime) as DateTime,a.deviceid
from 表 a,INSERTED b
WHERE a.deviceid=b.deviceid
GROUP BY a.deviceid) d
where c.deviceid=d.deviceid and c.DateTime=d.DateTime
for insert
as
delete a
from 表 a,(
select DeviceID,[DateTime]=min([DateTime])
from 表 a
where exists(select 1 from inserted where DeviceID=a.DeviceID)
group by DeviceID
having count(*)>10
)b where a.DeviceID=b.DeviceID and a.[DateTime]=b.[DateTime]
for insert
as
delete a
from 表 a,insert i
where a.DeviceID=i.DeviceID
and (select count(*) from 表 where DeviceID=a.DeviceID and [datetime]>a.[datetime])>=10
但是不一定在一起时间。
当VVD1 满十条记录。再来一条新的。我就必须把第一个来的数据VVD 给删除!谢谢!是这个意思!
最讨厌的是 DeviceID 每个数据不一样。
还要判断他(VVD2,VVD N) 是不是满10 条!!
CREATE TRIGGER [tr_del] ON 表
FOR insert
AS
if (select count(*) from 表 a,inserted b where a.deviceid=b.deviceid)>10
delete 表
from 表 c,
(select min(a.datetime) as DateTime,a.deviceid
from 表 a,INSERTED b
WHERE a.deviceid=b.deviceid
GROUP BY a.deviceid) d
where c.deviceid=d.deviceid and c.DateTime=d.DateTime