求 存储过程写法或 触发器写法。（视图更新或2表更新）

[lirenniao]

两个表，一开始用了视图，写的存储过程，但是 更新的时候出错，应该是没用触发器。。（不会写）
tb_House表：
id jid uptime
1 31 2010-8-25 15:25:03

tb_shuaxin表:
houseid intime upok
1 2010-8-25 14:10:31 0


要做的是 查询当前( id和 houseid 关联) tb_shuaxin表中 upok=0， intime如果比当前时间小或等于，就更新tb_House表的uptime为intime,tb_shuaxin表的upok=1

就是要 做一个 定时刷新时间的存储过程。
求 存储过程写法或 触发器写法。，，，，

[lirenniao]

快来人。。。。

[lass_name]

可以考虑用作业试试

[chen8410]

CREATE PROCEDURE P_test

AS
BEGIN

	SET NOCOUNT ON;
	if exists(select 1 from tb_shuaxin where upok=0 and intime<=GETDATE())
	begin
		update tb_House set uptime=tb_shuaxin.intime
		from tb_shuaxin
		where tb_House.id = tb_shuaxin.houseid and upok=0 and intime<=GETDATE()
		
		update tb_shuaxin set upok=1
		where upok=0 and intime<=GETDATE()		
	end
	
END
GO

上面是执行刷新的存储过程，要定时刷新的话可以使用作业定时执行存储过程，参考：
http://www.cnblogs.com/njnudt/archive/2007/04/27/729935.html

[duanzhi1984]

--触发器
create trigger tr_shuaxin
on tb_shuaxin for insert,update
as
begin
	if exists(select 1 from  inserted where intime<=getdate())
	begin
       update tb_House set uptime=intime from inserted where houseid=id

       update tb_shuaxin set upok=1 from inserted a where a.houseid=tb_shuaxin.houseid

	end
end

--过程
create proc tr_shuaxin
as
begin
	if exists(select 1 from  tb_shuaxin where intime<=getdate())
	begin
       update tb_House set uptime=intime from tb_shuaxin where houseid=id and intime<=getdate()

       update tb_shuaxin set upok=1 where intime<=getdate()

	end
end

[Andy__Huang]

create proc test
as

update
set upok=1,intime=case when intime<=a.uptime then a.uptime else intime end
from tb_shuaxin a
inner join tb_House b on a.houseid=b.id
where a.upok=0

go

[lirenniao]

呵呵，本来就是要 写进作业里面的，3楼的测试成功。。也感谢大家了，结贴给分。