sql 触发器 修改

Question

create table school
(
sid int primary key ,
sName nvarchar(50),
status int 
) create table teacher
(
tid int identity(1,1) primary key,
sid int ,
tName nvarchar(50),
status int
)insert into school values(1,'s1',1);
insert into school values(2,'s2',1);insert into teacher values(1,'t1',1);
insert into teacher values(1,'t2',1);
insert into teacher values(2,'t3',2);
insert into teacher values(2,'t4',2);alter table teacher
add constraint FK_teacher_sid foreign key(sid) references school(sid) 当修改school的状态时，school对应teacher的sid 的状态也修改
比如:
update school set status=100 where sid=1;
之后
teacher 与 school 关联的列 的 status修改成100

Answer 1

create tigger t_school  on school
as
begin
if update(school)
update teacher set sid=a.sid,status=a.status from inserted a join teacher b on a.sid=b.sid 
end

Answer 2

不行 if update(school)
会提示 列名 'school' 无效。如果把 school 改成 status 则teacher所以的状态都改了本来teacher初始数据是
tid         sid         tName                                              status
----------- ----------- -------------------------------------------------- 1           1           t1                                                 1
2           1           t2                                                 1
3           2           t3                                                 1
4           2           t4                                                 1update school set status=100 where sid=1;
之后希望得到
tid         sid         tName                                              status
----------- ----------- -------------------------------------------------- 1           1           t1                                                 100
2           1           t2                                                 100
3           2           t3                                                 1
4           2           t4                                                 1

Answer 3

create tigger t_school  on school
after update
as
begin
    if update(status)
       update a set a.status=b.status 
             from teacher a, inserted b where on a.sid=b.sid 
end

Answer 4

create trigger t_school  on school
after update
as
begin
    if update(status)
       update a set a.status=b.status 
             from teacher a, inserted b where on a.sid=b.sid 
end

Answer 5

create trigger t_school  on school
after update
as
begin
if update(status)
update b set b.status=a.status from inserted a join teacher b on a.sid=b.sid 
end

Answer 6

create table school
(
sid int primary key ,
sName nvarchar(50),
status int  
)  create table teacher
(
tid int identity(1,1) primary key,
sid int ,
tName nvarchar(50),
status int
)insert into school values(1,'s1',1);
insert into school values(2,'s2',1);insert into teacher values(1,'t1',1);
insert into teacher values(1,'t2',1);
insert into teacher values(2,'t3',2);
insert into teacher values(2,'t4',2);alter table teacher
add constraint FK_teacher_sid foreign key(sid) references school(sid) 
go
create trigger t_school on school after update
as
update teacher set status=b.status from teacher a inner join inserted b on a.sid=b.sid go
update school set status=100 where sid=1
select * from teacher
/*
tid         sid         tName                                              status
----------- ----------- -------------------------------------------------- -----------
1           1           t1                                                 100
2           1           t2                                                 100
3           2           t3                                                 2
4           2           t4                                                 2(4 行受影响)*/
go
drop table teacher
drop table school