触发器问题依然存在啊`哭的呢```
我想让它按近来的日期计算
从2007年1月1日开始进的
我现在想按照进来的时间删除,
不管多少天,比如:
(主键)timetest a b
2007-01-01 123 123
2007-02-10 123 123
按照时间差1月1日到2月10日有40天,当第一条减第二条时间大于等于40天的时候
我想删除1月1日到1月30日,
应该怎么写啊?下面的是前面那位zjcxc(邹建)大哥帮我写的.但是还是删除不了，也不知道我说错了还是哪地方错了.
求大哥们帮帮忙吧.哭了4天了,支撑不下去了,头都要顶烂了.
create trigger trg on DB2
for insert
as
declare @icount int
select @icount = count(*) from DB2
if (@icount = 40)
delete from db2
where timetest in(
select top 30 timetest from DB2 order by timetest)
[解决办法]
create trigger trg on DB2
for insert
as
declare @icount int
select @icount = datediff(d,min(timetest),max(timetest)) from DB2
if (@icount = 40)
delete from db2
where timetest in(
select top 30 timetest from DB2 order by timetest)
[解决办法]
邹建写的肯定没错，估计是你没描述清楚，还有，你数据库里的日期是连续的吗？如果不连续，结果和你所要的肯定不一样
[解决办法]
没说清楚，你给的是什么表(表名)，要删除的是哪张表(表的结构什么样,与这张表什么关系)，什么时候触发
[解决办法]
drop table DB2
Create table DB2 (timetest datetime , a int ,b int )
go
create trigger trg on DB2
for insert
as
declare @icount int
select @icount = datediff(d,min(timetest),max(timetest)) from DB2
if (@icount > = 40)
delete from db2
where timetest not in(
select top 10 timetest from DB2 order by timetest desc)
go
declare @a datetime
declare @i int
set @i=0
set @a= '2007-1-1 '
while @i <=40
begin
insert into DB2 values( dateadd(d,@i,@A),@i,@i)
set @i=@i+1
print @i
end
select * from DB2
delete from DB2
1月1日到2月10日 应该是41天
[解决办法]
create trigger trg on DB2
for insert
as
declare @icount int
select @icount = datediff(d,min(timetest),max(timetest)) from DB2
if (@icount > = 40)
delete from db2
where datediff(d,timetest,(select max(timetest) from DB2 ))> 10
[解决办法]
create table db2(timetst datetime,a varchar(10),b varchar(10))
insert db2 select '2007-01-01 ', '123 ', '123 '
insert db2 select '2007-01-02 ', '456 ', '123 '
insert db2 select '2007-01-30 ', '--- ', '123 '
insert db2 select '2007-01-31 ', '123 ', '456 '
go
-------------------------------
create trigger trg on DB2 instead of insert as
begin
if (select datediff(d,isnull(min(timetst), '2099-12-31 '),(select timetst from inserted)) from db2)> =40
delete db2 from db2,inserted a where datediff(d,db2.timetst,a.timetst)> 10
insert DB2 select * from inserted
end
----------------------------------
go
insert db2 select '2007-02-01 ', '789 ', '123 '
select * from db2
go
insert db2 select '2007-02-10 ', '123 ', '789 '
select * from db2
go
