现如今有二个表INVMB,BOMMDINVMB表结构如下:
MB001 UDF01
aaa 111
bbb 222
ccc 333BOMMD表结构如下:
MD001 MD016
aaa 333
bbb 444要的结果是,一旦INVMB表的UDF01发生任何变化,都要使BOMMD表的MD016与其一致,关联条件是MB001=MD001原数据插入CREATE TABLE INVMB(MB001 varchar(1000),UDF01 varchar(1000))
INSERT INVMB
SELECT 'aaa','111' UNION ALL
SELECT 'bbb','222' UNION ALL
SELECT 'ccc','333' CREATE TABLE BOMMD(MD001 varchar(1000),MD016 varchar(1000))
SELECT 'aaa','333' UNION ALL
SELECT 'bbb','444'
MB001 UDF01
aaa 111
bbb 222
ccc 333BOMMD表结构如下:
MD001 MD016
aaa 333
bbb 444要的结果是,一旦INVMB表的UDF01发生任何变化,都要使BOMMD表的MD016与其一致,关联条件是MB001=MD001原数据插入CREATE TABLE INVMB(MB001 varchar(1000),UDF01 varchar(1000))
INSERT INVMB
SELECT 'aaa','111' UNION ALL
SELECT 'bbb','222' UNION ALL
SELECT 'ccc','333' CREATE TABLE BOMMD(MD001 varchar(1000),MD016 varchar(1000))
SELECT 'aaa','333' UNION ALL
SELECT 'bbb','444'
CREATE TRIGGER trig_update ON INVMB
FOR UPDATE
ASif update(UDF01)
begin
update a set MD016=b.UDF01 from BOMMD a,inserted b where a.MD001=b.MB001end
create table invmb(mb001 varchar(20),udf01 varchar(20))
insert into invmb
select 'aaa','111' union all
select 'bbb','222' union all
select 'ccc','333'
gocreate table bommd(md001 varchar(20),md016 varchar(20))
insert into bommd
select 'aaa','333' union all
select 'bbb','444'
gocreate trigger up_invmb on invmb
for update
as
update a
set a.md016 = b.udf01
from bommd a join inserted b on a.md001 = b.mb001
goupdate invmb
set udf01 = 'jkl'
where mb001 = 'bbb'select *
from bommddrop trigger up_invmb
drop table invmb,bommd/*************md001 md016
-------------------- --------------------
aaa 333
bbb jkl(2 行受影响)
FOR INSERT,UPDATE
AS
SET NOCOUNT ON
BEGIN
DECLARE @MD001 varchar(1000),@MD016 varchar(1000)
SELECT @MD001=MD001,@MD016=MD016 FROM INSERTED
IF EXISTS(SELECT * FROM BOMMD WHERE w.MD001=@MD001)
BEGIN
UPDATE w
SET w.MD016=q.UDF01
FROM BOMMD w INNER JOIN INVMB q ON w.MD001=q.MB001
WHERE w.MD001=@MD001
END
END
SET NOCOUNT OFF
楼上只写有更新,应该还有插入吧,insert
INSERT INVMB
SELECT 'aaa','111' UNION ALL
SELECT 'bbb','222' UNION ALL
SELECT 'ccc','333' CREATE TABLE BOMMD(MD001 varchar(1000),MD016 varchar(1000))
INSERT INTO BOMMD(MD001,MD016)
SELECT 'aaa','333' UNION ALL
SELECT 'bbb','444'
SELECT * FROM invmb
SELECT * FROM bommd
CREATE TRIGGER Trg_INV on invmb
FOR UPDATE
AS
begin
IF UPDATE(UDF01)
UPDATE bommd
SET md016 = INVmb.UDF01
FROM invmb
WHERE invmb.MB001 = MD001
END
UPDATE dbo.INVMB
SET UDF01 = 'TTT'
WHERE MB001 = 'aaa'
INSERT INVMB
SELECT 'aaa','111' UNION ALL
SELECT 'bbb','222' UNION ALL
SELECT 'ccc','333' CREATE TABLE BOMMD(MD001 varchar(1000),MD016 varchar(1000))
insert into BOMMD
SELECT 'aaa','333' UNION ALL
SELECT 'bbb','444'
Create Trigger Tr_name on INVMB
for update
as
begin
if update(UDF01)
update a set MD016=b.UDF01
from BOMMD a,inserted b
where a.MD001=b.MB001
end
update INVMB set UDF01='999' where MB001='aaa'
select * from BOMMD
create table invmb(mb001 varchar(20),udf01 varchar(20))
insert into invmb
select 'aaa','111' union all
select 'bbb','222' union all
select 'ccc','333'
gocreate table bommd(md001 varchar(20),md016 varchar(20))
insert into bommd
select 'aaa','333' union all
select 'bbb','444' union all
select 'ddd',null --增加一列测试
gocreate trigger up_invmb on invmb
for insert,update
as
update a
set a.md016 = b.udf01
from bommd a join inserted b on a.md001 = b.mb001
goupdate invmb
set udf01 = 'jkl'
where mb001 = 'bbb'select *
from bommdinsert into invmb select 'ddd','123'select *
from bommddrop trigger up_invmb
drop table invmb,bommd/***********************md001 md016
-------------------- --------------------
aaa 333
bbb jkl
ddd NULL(3 行受影响)
md001 md016
-------------------- --------------------
aaa 333
bbb jkl
ddd 123(3 行受影响)楼主要把所有情况都说明下。
Create Trigger Tr_name on INVMB
for update,insert,delete
as
begin
delete a from BOMMD a,deleted b
where a.MB001=b.MB001 and a.md016=b.UDF01
insert into BOMMD
select * from inserted
end
INVMB表结构如下:
MB001 UDF01
aaa 111
bbb 222
ccc 333BOMMD表结构如下:
MD001 MD016
aaa 333
bbb 444写了触发器以后,数据如下:
INVMB表结构如下:
MB001 UDF01
aaa 111
bbb 222
ccc 333BOMMD表结构如下:
MD001 MD016
aaa 111
bbb 222以后我在BOMMD插入行,只要MD001=MB001,那么MD016必须要与UDF01一致
CREATE TRIGGER trig_update ON INVMB
FOR INSERT,UPDATE
AS
update a set MD016=b.UDF01 from BOMMD a,inserted b where a.MD001=b.MB001
if object_id('[invmb]') is not null drop table invmb
create table invmb(mb001 varchar(20),udf01 varchar(20))
insert into invmb
select 'aaa','111' union all
select 'bbb','222' union all
select 'ccc','333'
go
if object_id('[bommd]') is not null drop table bommd
create table bommd(md001 varchar(20),md016 varchar(20))
insert into bommd
select 'aaa','333' union all
select 'bbb','444'
goif object_id('xxoo') is not null drop trigger xxoo
go
create trigger xxoo on invmb
for insert,update
as
if exists(select 1 from bommd b ,inserted i where b.md001=i.mb001)
update b set b.md016=i.udf01 from bommd b,inserted i where b.md001=i.mb001
else
insert into bommd select mb001,udf01 from inserted
go
insert into invmb select 'ddd',112
update invmb set udf01='333' where mb001='aaa'
select * from bommd
go
create trigger xxoo on invmb
for insert,update
as
delete b from bommd b,inserted i where b.md001=i.mb001
insert into bommd select mb001,udf01 from inserted
go
create table invmb(mb001 varchar(20),udf01 varchar(20))
insert into invmb
select 'aaa','111' union all
select 'bbb','222' union all
select 'ccc','333'
gocreate table bommd(md001 varchar(20),md016 varchar(20))
insert into bommd
select 'aaa','333' union all
select 'bbb','444' union all
select 'ddd',null --增加一列测试
gocreate trigger up_invmb on invmb
for insert,update,delete
as
if exists (select 1 from inserted)
begin
update a
set a.md016 = b.udf01
from bommd a join inserted b on a.md001 = b.mb001
end
else
begin
update a
set a.md016 = null
from bommd a join deleted b on a.md001 = b.mb001
end
goupdate invmb
set udf01 = 'jkl'
where mb001 = 'bbb'select *
from bommdinsert into invmb select 'ddd','123'select *
from bommddelete from invmb where mb001 = 'aaa'select *
from bommddrop trigger up_invmb
drop table invmb,bommd
/******************md001 md016
-------------------- --------------------
aaa 333
bbb jkl
ddd NULL(3 行受影响)(1 行受影响)
(1 行受影响)
md001 md016
-------------------- --------------------
aaa 333
bbb jkl
ddd 123(3 行受影响)(1 行受影响)
(1 行受影响)
md001 md016
-------------------- --------------------
aaa NULL
bbb jkl
ddd 123(3 行受影响)
go
create trigger xxoo on invmb
for insert,update,delete
as
if exists(select 1 from deleted) and not exists(select 1 from inserted)
delete b from bommd b,deleted d where b.md001=d.mb001
else
delete b from bommd b,inserted i where b.md001=i.mb001
insert into bommd select mb001,udf01 from inserted
go
insert into invmb select 'ddd',112
update invmb set udf01='333' where mb001='aaa'
delete from invmb where mb001='bbb'
select * from bommd--------带删除的最后一次路过。
FOR UPDATE
AS
SET NOCOUNT ON
IF UPDATE(UDF01)
BEGIN
UPDATE a
SET a.MD016=b.UDF01
FROM BOMMD a INNER JOIN INSERTED b ON a.MD003=b.MB001
END
SET NOCOUNT OFF