我有三张表
mjlb_message
moju_message
mjbj_messagemjlb_message 的 mjlb_id 和moju_message 的mojlb_id关联moju_message 的moju_number 和mjbj_message的mjbj_bjmj关联修改mjlb_message表中的 mjlb_test1 也会修改到moju_message表中的moju_test1再修改 mjbj_message表中的mjbj_test1这个列
各位大侠 我该怎么写?谢谢各位了!
mjlb_message
moju_message
mjbj_messagemjlb_message 的 mjlb_id 和moju_message 的mojlb_id关联moju_message 的moju_number 和mjbj_message的mjbj_bjmj关联修改mjlb_message表中的 mjlb_test1 也会修改到moju_message表中的moju_test1再修改 mjbj_message表中的mjbj_test1这个列
各位大侠 我该怎么写?谢谢各位了!
try.
CREATE TRIGGER tr ON mjlb_message
AFTER UPDATE
AS
BEGIN
IF UPDATE(mjlb_test1)
BEGIN
UPDATE moju_message SET moju_test=INSERTED.[mjlb_test1]
FROM INSERTED,moju_message A,mjbj_message B
WHERE INSERTED.mjlb_id=A.moju_id AND B.mjbj_bjmj=A.moju_number
UPDATE mjbj_message SET mjbj_test1=INSERTED.[mjlb_test1]
FROM INSERTED,moju_message A,mjbj_message B
WHERE INSERTED.mjlb_id=A.moju_id AND B.mjbj_bjmj=A.moju_number
END;
END;
create table moju_message(mojlb_id int,moju_number int,moju_message nvarchar(10),moju_test1 nvarchar(10))
create table mjbj_message(mjbj_bjmj int,mjbj_test1 nvarchar(10))
insert into mjlb_message select 1,'aa' union all select 2,'bb'
insert into moju_message select 1,10,'abcd','aa' union all select 2,11,'mivw','bb'
insert into mjbj_message select 10,'aa' union all select 11,'bb'
go
create trigger updatetwotablescolumn
on mjlb_message
after update
as
begin
SET NOCOUNT ON; update moju_message set moju_test1=b.mjlb_test1 from moju_message a inner join inserted b on a.mojlb_id=b.mjlb_id
update mjbj_message set mjbj_test1=c.mjlb_test1
from mjbj_message a inner join moju_message b on a.mjbj_bjmj=b.moju_number inner join inserted c on b.mojlb_id=c.mjlb_id
end
go
update mjlb_message set mjlb_test1='gogogo' where mjlb_id=1
select * from mjlb_message
select * from moju_message
select * from mjbj_message
go
drop table mjlb_message,moju_message,mjbj_message
/*
mjlb_id mjlb_test1
----------- ----------
1 gogogo
2 bb(2 行受影响)mojlb_id moju_number moju_message moju_test1
----------- ----------- ------------ ----------
1 10 abcd gogogo
2 11 mivw bb(2 行受影响)mjbj_bjmj mjbj_test1
----------- ----------
10 gogogo
11 bb(2 行受影响)*/