---------------------------------------------------------------------
A
CODE TIME DATE DATA
5000 19:53:40 2008-09-10 6
5000 19:53:41 2008-09-10 6.5
5000 19:53:42 2008-09-10 5.5
5000 19:53:43 2008-09-10 6
0027 20:23:55 2008-09-24 7.1
0027 20:23:57 2008-09-24 7.2
0027 20:23:58 2008-09-24 7.7
0027 20:24:03 2008-09-24 7.3
5027 20:16:30 2008-09-27 8.4
5027 20:16:28 2008-09-27 8.1
5027 20:16:51 2008-09-27 8 ----------------------------------------------------------------------
B
CODE DATA TIME DATE
5000 20 14:37:34 2008-09-01
5000 10 08:02:17 2008-07-10
0027 20 12:05:59 2008-09-24
0027 20 10:03:21 2008-08-01
0027 10 08:00:10 2008-08-10
5027 20 11:07:00 2008-09-27
5027 15 09:09:00 2008-09-30
5027 10 10:07:00 2008-05-21
--------------------------------------------------------------------
上面是统一数据库里的A和B两个表,唯一相同的就是CODE,我想在更新A表DATA的同时,B表里与A表CODE相同的也同时做相应更新,请帮忙写出建立触发器的命令,如果有其他方法请帮忙!!!!!!!
A
CODE TIME DATE DATA
5000 19:53:40 2008-09-10 6
5000 19:53:41 2008-09-10 6.5
5000 19:53:42 2008-09-10 5.5
5000 19:53:43 2008-09-10 6
0027 20:23:55 2008-09-24 7.1
0027 20:23:57 2008-09-24 7.2
0027 20:23:58 2008-09-24 7.7
0027 20:24:03 2008-09-24 7.3
5027 20:16:30 2008-09-27 8.4
5027 20:16:28 2008-09-27 8.1
5027 20:16:51 2008-09-27 8 ----------------------------------------------------------------------
B
CODE DATA TIME DATE
5000 20 14:37:34 2008-09-01
5000 10 08:02:17 2008-07-10
0027 20 12:05:59 2008-09-24
0027 20 10:03:21 2008-08-01
0027 10 08:00:10 2008-08-10
5027 20 11:07:00 2008-09-27
5027 15 09:09:00 2008-09-30
5027 10 10:07:00 2008-05-21
--------------------------------------------------------------------
上面是统一数据库里的A和B两个表,唯一相同的就是CODE,我想在更新A表DATA的同时,B表里与A表CODE相同的也同时做相应更新,请帮忙写出建立触发器的命令,如果有其他方法请帮忙!!!!!!!
對應的條件還有別的沒?
on a
for update
as
begin
update b
set DATA=DATA+i.data
from
(select code,convert(varchar(10),[date],120) as [date],sum(data) as data from inserted group by code,convert(varchar(10),[date],120)) i
where b.code=i.code and convert(varchar(10),[date],120)=b.[date]
endgo
以上只适合B中表一个code一天只有一条记录的情况,如果b表一个code一天有多条记录的话要怎么对应,这个需要楼主考虑了。
on a
for update
as
begin
update b
set b.DATA=b.DATA+i.data
from
(select code,convert(varchar(10),[date],120) as [date],sum(data) as data from inserted group by code,convert(varchar(10),[date],120)) i
where b.code=i.code and convert(varchar(10),b.[date],120)=i.[date]
endgo[/code]
这样比较好理解
---------------------------------------------------------------------
A
CODE TIME DATE DATA
5000 19:53:40 2008-09-10 1
5000 19:53:41 2008-09-10 1.5
5000 19:53:42 2008-09-10 0.5
5000 19:53:43 2008-09-10 1
0027 20:23:55 2008-09-24 2.1
0027 20:23:57 2008-09-24 2.2
0027 20:23:58 2008-09-24 2.7
0027 20:24:03 2008-09-24 2.3
5027 20:16:30 2008-09-27 3.4
5027 20:16:28 2008-09-27 3.1
5027 20:16:51 2008-09-27 3 ----------------------------------------------------------------------
B
CODE DATA TIME DATE
5000 0 14:37:34 2008-09-01
5000 10 08:02:17 2008-07-10
0027 0 12:05:59 2008-09-24
0027 20 10:03:21 2008-08-01
0027 10 08:00:10 2008-08-10
5027 5 11:07:00 2008-09-27
5027 15 09:09:00 2008-09-30
5027 10 10:07:00 2008-05-21
--------------------------------------------------------------------
declare @str varchar(100)
--定义游标
declare DZCursor CURSOR Local Fast_forward READ_ONLY for SELECT test_str FROM test where test_str='xxx'
--打开游标
open DZCursor
--从游标取记录
fetch next from DZCursor into @str
--当有记录
while @@fetch_status=0
begin
--你的语句
--取下一条记录
fetch next from DZCursor into @str
end
--关闭游标
close DZCursor
--删除游标引用
deallocate DZCursor
on a
for update
as
declare @code varchar(5)
declare @before_data int
declare @after_date int
select @code=code,@after_data=data from inserted
select @before_data=data from deleted
update b set data=data+(@after_data-@before_data) where code=@code
go--测试数据
update a set data=data+5 where code='5000'
create trigger tri_a_update
on a
for update
as
declare @code varchar(5)
declare @before_data int
declare @after_date int
select @code=code,@after_data=data from inserted
select @before_data=data from deleted
update b set data=data+(@after_data-@before_data) where code=@code
go
ALTER TABLE tbname DISABLE TRIGGER trigname
--启用触发器这样。
ALTER TABLE tbname enable TRIGGER trigname
---------------------------------------------------------------------
A
CODE TIME DATE DATA
5000 19:53:40 2008-09-10 6
5000 19:53:41 2008-09-10 6.5
5000 19:53:42 2008-09-10 5.5
5000 19:53:43 2008-09-10 6
0027 20:23:55 2008-09-24 7.1
0027 20:23:57 2008-09-24 7.2
0027 20:23:58 2008-09-24 7.7
0027 20:24:03 2008-09-24 7.3
5027 20:16:30 2008-09-27 8.4
5027 20:16:28 2008-09-27 8.1
5027 20:16:51 2008-09-27 8 ----------------------------------------------------------------------
B
CODE PDATA PTIME PDATE
5000 20 14:37:34 2008-09-01
5000 10 08:02:17 2008-07-10
0027 20 12:05:59 2008-09-24
0027 20 10:03:21 2008-08-01
0027 10 08:00:10 2008-08-10
5027 20 11:07:00 2008-09-27
5027 15 09:09:00 2008-09-30
5027 10 10:07:00 2008-05-21
--------------------------------------------------------------------
根据上表,A表DATA减去5,B表PDATA也减去对应的数,两个表里唯一对应的列是CODE,下面是我希望达到的效果。
---------------------------------------------------------------------
A
CODE TIME DATE DATA
5000 19:53:40 2008-09-10 1
5000 19:53:41 2008-09-10 1.5
5000 19:53:42 2008-09-10 0.5
5000 19:53:43 2008-09-10 1
0027 20:23:55 2008-09-24 2.1
0027 20:23:57 2008-09-24 2.2
0027 20:23:58 2008-09-24 2.7
0027 20:24:03 2008-09-24 2.3
5027 20:16:30 2008-09-27 3.4
5027 20:16:28 2008-09-27 3.1
5027 20:16:51 2008-09-27 3 ----------------------------------------------------------------------
B
CODE PDATA PTIME PDATE
5000 10 08:02:17 2008-07-10
0027 20 10:03:21 2008-08-01
0027 10 08:00:10 2008-08-10
5027 5 11:07:00 2008-09-27
5027 15 09:09:00 2008-09-30
5027 10 10:07:00 2008-05-21
--------------------------------------------------------------------
跪求高手帮助,请教建立触发器和进行更新的详细命令。