我有一个表:
id name order pubdate flag
1 aaa a122 2007-10-10 1
2 bbb a1223 2007-10-11 1
3 ccc 1224 2007-10-12 1
4 ddd a122 2007-10-20 0我的问题是这样的,如果pubdate的日期到了2007-10-30 ,则flag值就自动设为0谢谢各位大侠!在线等!
id name order pubdate flag
1 aaa a122 2007-10-10 1
2 bbb a1223 2007-10-11 1
3 ccc 1224 2007-10-12 1
4 ddd a122 2007-10-20 0我的问题是这样的,如果pubdate的日期到了2007-10-30 ,则flag值就自动设为0谢谢各位大侠!在线等!
ON table_name
FOR UPDATE ,INSERT
AS
update table_name set flag=0 where pubdate='2007-10-30'
after update,insert
as
begin
update t set flag=0 where pubdate >= '2007-10-30 'end
go
insert into tablename
select '2007-10-01' union
select '2007-11-01' union
select '2007-12-01'alter table tablename add flag1 as case when pubdate>'2007-10-31' then 1 else 0 endselect * from tablename
drop table tablename/*--
pubdate flag1
----------------------- -----------
2007-10-01 00:00:00.000 0
2007-11-01 00:00:00.000 1
2007-12-01 00:00:00.000 1
--*/
ON table_name
FOR UPDATE ,INSERT
AS
update table_name set flag=0 where pubdate= '2007-10-30 ' ======================如果用这个触发器,估计99.99%是无效触发
这个应该是作业,类似计划任务,你安排在凌晨运行即可 管理--sqlsever代理--作业
“fa_ge”大侠,我是这样使用作业调度:打开sql2000->工具->作业调度->创建作业向导->选择“Transact-SQL”->“Transact-SQL 语句”->输入你给的语句......后打开数据库,查看 flag 还是没有变为“0”
(2)而“jinjazz ”大侠说的公式字段不知道如何使用(3)“cs_oldhorse ”大侠的数据库代理,小弟上网查过,很少这方面的资料!各位大侠,可以再帮帮小弟吗......, 万分感激!!
就可以把flag=0 的.
我刚开始说用作业执行,因为它不能实时更新,所以还是要触发器来解决.
go
insert into t
select '2007-10-01',null union
select '2007-11-01',null union
select '2007-12-01',null--创建触发器
create trigger tri_update on t
after update,insert
as
begin
update t set flag=0 where pubdate >= '2007-10-30' end--最首记录select * from tpubdate flag
------------------------------------------------------ -----------
2007-10-01 00:00:00.000 NULL
2007-11-01 00:00:00.000 NULL
2007-12-01 00:00:00.000 NULL(所影响的行数为 3 行)
--先插入一条记录,时间='2007-10-30 'insert into t values('2007-10-30',null)select * from tpubdate flag
------------------------------------------------------ -----------
2007-10-01 00:00:00.000 NULL
2007-11-01 00:00:00.000 0
2007-12-01 00:00:00.000 0
2007-10-30 00:00:00.000 0(所影响的行数为 4 行)--我们更新第一条记录,时间='2007-10-30'update t
set pubdate='2007-10-30'
where flag is nullselect * from tpubdate flag
------------------------------------------------------ -----------
2007-10-30 00:00:00.000 0
2007-11-01 00:00:00.000 0
2007-12-01 00:00:00.000 0
2007-10-30 00:00:00.000 0(所影响的行数为 4 行)
我在触发器设的条件是 pubdate >= '2007-10-30 ',你可以改为 pubdate = '2007-10-30 '
flag=0
where convert(varchar(10),date,120)=convert(varchar(10),getdate(),120)
或update t
flag=0
where datediff(day,getdate(),date)=0