这个更新的触发器怎么写?
有两个表
Table_A
tid ttype date
1 11 07.02.01
2 12 07.02.01
3 13 07.03.01
Table_B
tid count date
1 0 0702
2 0 0705
3 0 0706
两个表 tid 字段为对应,现在我要在Table_A表中添加触发器,当 ttype 字段有更新时,需要更新相对Table_B中的 count 字段,相对应的关系是
更新后ttype的值 count值
21 100
22 300
23 500
这个触发如何来写,请高人指点迷津,呵呵!
------解决方案-------------------------例子-----
create table Table_A(tid int,ttype int,date varchar(10))
insert Table_A
select 1,11, '07.02.01 '
union select 2,12, '07.02.01 '
union select 3,13, '07.03.01 '
create table Table_B(tid int,count int,date varchar(10))
insert Table_B
select 1,0, '0702 '
union select 2,0, '0705 '
union select 3,0, '0706 '
create trigger t_auto_update on Table_A
for update
as
begin
update b
set b.[count]=case a.ttype when 21 then 100 when 22 then 300 when 23 then 500 end
from inserted a inner join Table_B b on a.tid=b.tid
end
update Table_A
set ttype =21
where tid=1
select * from Table_A
select * from Table_B
drop table Table_A,Table_B
------解决方案--------------------你更新A表后的ttype一定是21,22,23这三个数值吗?
CREATE TRIGGER up_a ON table_a
FOR UPDATE
AS
declare @row int
if @row=0 return
if exists (select ttype from inserted)
begin
update Table_B
set count=(case ttype when 21 then 100
when 22 then 300 when 23 then 500 end )
from inserted i,table_b b
where i.tid=b.tid
end