有一个表a
结构:
point int ,counter int ,username nvarchar还有一个表b结构:
score int,editline int, username nvarchar要设计一个触发器,
当a表中数据做更新(update)时候,
要把a表中做更新这条记录的
point,counter,username
插入到表b中的
score,editline,username
中去....
哪位高手帮帮忙忙,急!!!
结构:
point int ,counter int ,username nvarchar还有一个表b结构:
score int,editline int, username nvarchar要设计一个触发器,
当a表中数据做更新(update)时候,
要把a表中做更新这条记录的
point,counter,username
插入到表b中的
score,editline,username
中去....
哪位高手帮帮忙忙,急!!!
alter table b add uptime datetime
go
create trigger tr_test on a
for update
as
insert b
select *,getdate() from inserted
GO
create trigger tru_a
on 表a
for update
as
beginupdate t set t.score = i.point,t.editline = i.counter
from 表b t, inserted i where t.username=i.username and exists(select 1 from 表b where 表b.username=inserted.username)insert into 表b select * from inserted where username not in(select username from 表b)end
on 表a
for update
as
begin
set nocount on
update t set t.score = i.point,t.editline = i.counter
from 表b t, inserted i where t.username=i.username and exists(select 1 from 表b where 表b.username=inserted.username)
insert into 表b select * from inserted where username not in(select username from 表b)
set nocount off
end
drop trigger tr_from_a_to_b
go create trigger tr_from_a_to_b
on a
for update
as
insert b(score, editline, username)
select point, counter, username
from deleted
go
on a
for update
as
insert b(score, editline, username)
select point, counter, username
from deleted
go是只修改3个字段 才触发么?
谢谢,不过你这个当表B存在自增长ID时候会报错,提把INSERT改一下才好用..