if object_id('[a]') is not null drop table [a]
create table a
(
[id] int not null IDENTITY(1,1),
age int
)if object_id('[b]') is not null drop table [b]
create table b
(
[id] int not null IDENTITY(1,1),
age datetime
)insert into a (age) values (12)
insert into b (age) values ('2000-01-01')
go
Select * From a
Select * From b
/*目的:用约束的方式实现当b表age字段修改时相应的更新a表的age字段。
比如2001-01-01改成1999-01-01则a表age应该变成13*/
create table a
(
[id] int not null IDENTITY(1,1),
age int
)if object_id('[b]') is not null drop table [b]
create table b
(
[id] int not null IDENTITY(1,1),
age datetime
)insert into a (age) values (12)
insert into b (age) values ('2000-01-01')
go
Select * From a
Select * From b
/*目的:用约束的方式实现当b表age字段修改时相应的更新a表的age字段。
比如2001-01-01改成1999-01-01则a表age应该变成13*/
create trigger tr_b_update on b
for update
as
begin
update a set age = d.age
from a s inner join
(select id ,datediff(year,age,getdate()) as age from inserted) d on s.id = d.id
end
create trigger tri_b_upd
on b
for update
as
if update(age)
begin
update a set age=datediff(yy,i.age,getdate())
from inserted i
where a.id=i.id
end
goupdate b set age='1999-01-01' where id=1
goselect * from a
/**
id age
----------- -----------
1 13(1 行受影响)
**/
CREATE Trigger tr_b_update
on b
for update
as
if update(age)
begin
update a
set age=datediff(year,x.age,getdate())
from inserted x
where a.id=x.id
end
go