CREATE TRIGGER UPD_A ON A FOR UPDATE IF UPDATE(ID) UPDATE B SET ID=I.ID WHERE INSERTED I ON B.KEY=I.KEY然后把a,b調換就行了
就是有一个where条件,然后把a,b表中符合这个条件的数据,ID全部改成新的ID
if object_id('ta')is not null drop table ta go create table ta(No int identity,ID int ,[Name] varchar(10)) insert ta select 1,'A' union all select 2,'B' union all select 3,'C' if object_id('tb')is not null drop table tb go create table tb(No int identity,ID int ,[Name] varchar(10)) insert tb select 1,'A' union all select 2,'B' union all select 3,'C' go if object_id('Tri_update')is not null drop trigger Tri_update go create trigger Tri_update on ta for update as update tb set id=d.id from inserted d where tb.no=d.no go update ta set id=10 where id=2 select * from tb /*No ID Name ----------- ----------- ---------- 1 1 A 2 10 B 3 3 C*/
update a set id=@id
update b set id=@id
FOR UPDATE
IF UPDATE(ID)
UPDATE B SET ID=I.ID
WHERE INSERTED I
ON B.KEY=I.KEY然后把a,b調換就行了
就是有一个where条件,然后把a,b表中符合这个条件的数据,ID全部改成新的ID
go
create table ta(No int identity,ID int ,[Name] varchar(10))
insert ta select 1,'A' union all select 2,'B'
union all select 3,'C'
if object_id('tb')is not null drop table tb
go
create table tb(No int identity,ID int ,[Name] varchar(10))
insert tb select 1,'A' union all select 2,'B'
union all select 3,'C'
go
if object_id('Tri_update')is not null drop trigger Tri_update
go
create trigger Tri_update on ta
for update
as
update tb set id=d.id from inserted d where tb.no=d.no
go
update ta set id=10 where id=2
select * from tb
/*No ID Name
----------- ----------- ----------
1 1 A
2 10 B
3 3 C*/
1.在外面做一个存储过程也可以
2.如果是对A表改动,B表也自动改动,那么就在A表上做触发器,同样对B表
3.如果是对A,或B中任意一表做改动,另一表自动改动,就要加两个触发器,但是是否会触发死循环,我没有测试过