新建了一个触发器,功能是当更新某字段时自动修改另一张表里某字段的值. 具体代码如下.
遇到的问题是我更新cardinfo表里的所有数据时这个触发器也只更新一行. 这个是神马问题
alter trigger tri_cardinfo_update on cardInfo
for update
as
if update(userBirthday) or update(usersex)
begin
declare @cid varchar(50)
select @cid=cf.card_id from cardinfo as cf inner join inserted as i on cf.card_id=i.card_id
update dbo.ud_fileds set cx1=case
when year(getdate()) - year(cf.userBirthday)<25 and cf.usersex='男' then '小伙'
when year(getdate()) - year(cf.userBirthday)>=25 and cf.usersex='男' then '先生'
when year(getdate()) - year(cf.userBirthday)<25 and cf.usersex='女' then '小姐'
when year(getdate()) - year(cf.userBirthday)>=25 and cf.usersex='女' then '女士' else ''end
from dbo.ud_fileds as ud inner join cardinfo as cf on cf.card_id=ud.card_id where cf.card_id=@cid
end
if update(userBirthday) or update(usersex)
begin
declare @cid1 varchar(50)
select @cid1=card_id from inserted
update dbo.ud_fileds set cx2=case
when year(getdate()) - year(cf.userBirthday)<25 and cf.usersex='男' then '小伙'
when year(getdate()) - year(cf.userBirthday)>=25 and cf.usersex='男' then '先生'
when year(getdate()) - year(cf.userBirthday)<25 and cf.usersex='女' then '小姐'
when year(getdate()) - year(cf.userBirthday)>=25 and cf.usersex='女' then '女士' else ''end
from dbo.ud_fileds as ud inner join cardinfo as cf on cf.card_id=ud.card_id where cf.card_id=@cid1
endupdate cardinfo set usersex='女'
遇到的问题是我更新cardinfo表里的所有数据时这个触发器也只更新一行. 这个是神马问题
alter trigger tri_cardinfo_update on cardInfo
for update
as
if update(userBirthday) or update(usersex)
begin
declare @cid varchar(50)
select @cid=cf.card_id from cardinfo as cf inner join inserted as i on cf.card_id=i.card_id
update dbo.ud_fileds set cx1=case
when year(getdate()) - year(cf.userBirthday)<25 and cf.usersex='男' then '小伙'
when year(getdate()) - year(cf.userBirthday)>=25 and cf.usersex='男' then '先生'
when year(getdate()) - year(cf.userBirthday)<25 and cf.usersex='女' then '小姐'
when year(getdate()) - year(cf.userBirthday)>=25 and cf.usersex='女' then '女士' else ''end
from dbo.ud_fileds as ud inner join cardinfo as cf on cf.card_id=ud.card_id where cf.card_id=@cid
end
if update(userBirthday) or update(usersex)
begin
declare @cid1 varchar(50)
select @cid1=card_id from inserted
update dbo.ud_fileds set cx2=case
when year(getdate()) - year(cf.userBirthday)<25 and cf.usersex='男' then '小伙'
when year(getdate()) - year(cf.userBirthday)>=25 and cf.usersex='男' then '先生'
when year(getdate()) - year(cf.userBirthday)<25 and cf.usersex='女' then '小姐'
when year(getdate()) - year(cf.userBirthday)>=25 and cf.usersex='女' then '女士' else ''end
from dbo.ud_fileds as ud inner join cardinfo as cf on cf.card_id=ud.card_id where cf.card_id=@cid1
endupdate cardinfo set usersex='女'
通过这个来执行更新,肯定是满足这个条件的记录数,就是你说的1条更新所有的,必须通过inseted 或者deleted 来关联更新
for update
as
if update(userBirthday) or update(usersex)
begin
update dbo.ud_fileds set cx1=case
when year(getdate()) - year(cf.userBirthday)<25 and cf.usersex='男' then '小伙'
when year(getdate()) - year(cf.userBirthday)>=25 and cf.usersex='男' then '先生'
when year(getdate()) - year(cf.userBirthday)<25 and cf.usersex='女' then '小姐'
when year(getdate()) - year(cf.userBirthday)>=25 and cf.usersex='女' then '女士' else ''end
from dbo.ud_fileds as ud inner join cardinfo as cf on cf.card_id=ud.card_id
inner join inserted as i on cf.card_id=i.card_id
end
if update(userBirthday) or update(usersex)
begin
select @cid1=card_id from inserted
update dbo.ud_fileds set cx2=case
when year(getdate()) - year(cf.userBirthday)<25 and cf.usersex='男' then '小伙'
when year(getdate()) - year(cf.userBirthday)>=25 and cf.usersex='男' then '先生'
when year(getdate()) - year(cf.userBirthday)<25 and cf.usersex='女' then '小姐'
when year(getdate()) - year(cf.userBirthday)>=25 and cf.usersex='女' then '女士' else ''end
from dbo.ud_fileds as ud inner join cardinfo as cf on cf.card_id=ud.card_id inner join inserted as i on cf.card_id=i.card_id end看不出这两个if什么区别?
for update
as
if update(userBirthday) or update(usersex)
begin
update dbo.ud_fileds set cx1=case
when year(getdate()) - year(cf.userBirthday)<25 and cf.usersex='男' then '小伙'
when year(getdate()) - year(cf.userBirthday)>=25 and cf.usersex='男' then '先生'
when year(getdate()) - year(cf.userBirthday)<25 and cf.usersex='女' then '小姐'
when year(getdate()) - year(cf.userBirthday)>=25 and cf.usersex='女' then '女士' else ''end
,cx2=case
when year(getdate()) - year(cf.userBirthday)<25 and cf.usersex='男' then '小伙'
when year(getdate()) - year(cf.userBirthday)>=25 and cf.usersex='男' then '先生'
when year(getdate()) - year(cf.userBirthday)<25 and cf.usersex='女' then '小姐'
when year(getdate()) - year(cf.userBirthday)>=25 and cf.usersex='女' then '女士' else ''end
from dbo.ud_fileds as ud inner join cardinfo as cf on cf.card_id=ud.card_id inner join inserted as i on cf.card_id=i.card_id
end这样岂不省事