表dgdcc_lpzy的结构:
lp_name username condition
景湖春天 wlw1024 0
表dgdcc_loupan
lp_name mlp_username
景湖春天 ice241018要求是:
当condition由0变为1时(也就是更新condition的值),此时将mlp_username更新为wlw1024,两个表通过lp_name来关联.不知道我说清楚没有,解决马上结帖,谢谢了。
lp_name username condition
景湖春天 wlw1024 0
表dgdcc_loupan
lp_name mlp_username
景湖春天 ice241018要求是:
当condition由0变为1时(也就是更新condition的值),此时将mlp_username更新为wlw1024,两个表通过lp_name来关联.不知道我说清楚没有,解决马上结帖,谢谢了。
for update
as
update a set mlp_username = i.username
from dgdcc_loupan a
inner join inserted i on a.lp_name = i.lp_name
where i.condition = 1
GO
for update
as
update a set mlp_username = i.username
from dgdcc_loupan a , inserted i
where a.lp_name = i.lp_name and i.condition = 1
GO
结构如下:
lp_name lp_username
景湖春天 ice241018在完成上面要求的基础上,将lp_name也更新为wlw1024,谢谢各位了。
from dgdcc_loupan a
inner join inserted i on a.lp_name = i.lp_name
where i.condition = 1
GO
如果是改了lp_name字段但condition字段未变且为1,难道也要触发吗?又如同是更改了lp_name 和 condition字段呢?
1condition是否只有两种值(0,1)
2如果两个字段同时改了,怎样更新另一表?
lp_name username condition
景湖春天 wlw1024 0
表dgdcc_loupan:
lp_name mlp_username
景湖春天 ice241018表dgdcc_dictionary:
lp_name lp_username
景湖春天 ice241018
要求是:
当condition由0变为1时(也就是更新condition的值),此时将mlp_username和lp_username更新为wlw1024,三个表通过lp_name来关联。
for update
as
update a set mlp_username = i.username
from dgdcc_loupan a , inserted i
where a.lp_name = i.lp_name and i.condition = 1update a set lp_username = i.username
from dgdcc_dictionary a , inserted i
where a.lp_name = i.lp_name and i.condition = 1GO
for update
as
begin
update a set mlp_username = i.username
from dgdcc_loupan a , inserted i
where a.lp_name = i.lp_name and i.condition = 1update a set lp_username = i.username
from dgdcc_dictionary a , inserted i
where a.lp_name = i.lp_name and i.condition = 1
end
GO
我来晚了
insert dgdcc_lpzy
select '景湖春天' , 'wlw1024' , 0create table dgdcc_loupan(lp_name varchar(50) , mlp_username varchar(20))
insert dgdcc_loupan
select '景湖春天' , 'wlw1024'
create table dgdcc_dictionary(lp_name varchar(50) , lp_username varchar(20))
insert dgdcc_dictionary
select '景湖春天' , 'wlw1024' create trigger roy_tri on dgdcc_lpzy
FOR UPDATE
as
if update(condition)
begin
update dgdcc_loupan
set mlp_username=i.username
from deleted d inner join inserted i on d.lp_name=i.lp_name
where d.condition=0 and dgdcc_loupan.lp_name=i.lp_nameupdate dgdcc_dictionary
set lp_username=i.username
from deleted d inner join inserted i on d.lp_name=i.lp_name
where d.condition=0 and dgdcc_dictionary.lp_name=i.lp_name
endupdate dgdcc_lpzy
set condition=1select * from dgdcc_dictionary
select * from dgdcc_loupan
select * from dgdcc_lpzy
结果:
lp_name lp_username
-------------------------------------------------- --------------------
景湖春天 wlw1024(所影响的行数为 1 行)lp_name mlp_username
-------------------------------------------------- --------------------
景湖春天 wlw1024(所影响的行数为 1 行)lp_name username condition
-------------------------------------------------- -------------------- ---------
景湖春天 wlw1024 1(所影响的行数为 1 行)