我追问一句:为什么这样可以
declare @UserID nvarchar(20),@old_UserID nvarchar(20)
select @UserID=UserID from inserted
select @old_UserID=UserID from deleted
update NewsUserList set UserID=@UserID where UserID=@old_UserID
下面就不可以呢?
update NewsUserList set UserID=UserID from inserted where UserID=UserID from deleted
为什么一定要用变量过度一下?
CREATE trigger tr3
on userlist
for update
as
declare @UserID nvarchar(20),@old_UserID nvarchar(20)
select @UserID=UserID from inserted
select @old_UserID=UserID from deleted
update NewsUserList set UserID=@UserID where UserID=@old_UserID
--建表
create table userlist(
userid int identity,
username varchar(10),
age bit,
adrress varchar(50)
)
go
create table newsuserlist(
userid int identity,
username varchar(10),
age bit,
adrress varchar(50)
)
go
--插入数据
insert into userlist values('旺东',1,'三等功哈')
insert into userlist values('阿才',0,'阿瑟疯掉疯掉撒')
insert into userlist values('王五',1,'阿瑟风大送分')
insert into userlist values('张三',0,'阿 更好发福')
insert into userlist values('李四',1,'黄澄澄复活')
insert into newsuserlist values('旺东',1,'三等功哈')
insert into newsuserlist values('阿才',0,'阿瑟疯掉疯掉撒')
insert into newsuserlist values('王五',1,'阿瑟风大送分')
insert into newsuserlist values('张三',0,'阿 更好发福')
insert into newsuserlist values('李四',1,'黄澄澄复活')
go
--建立触发器
alter trigger tr_updatename on userlist
after update
as
begin
if @@rowcount=0
return --没有影响,则退出
if update(username)
update newsuserlist set username=deleted.username
from deleted
where deleted.userid=newsuserlist.userid
end
go
update userlist set username='王五1'
where userid=3
select * from userlist
select * from newsuserlist
drop trigger tr_updatename
drop table userlist,newsuserlist
--创建测试环境
create table userlist(ID int identity,userid varchar(20) primary key,username varchar(20))
create table newsuserlist(id int primary key,userlist varchar(200))
insert userlist(userid,username)
select 'A001','张三' union
select 'A002','李四' union
select 'A003','王五' union
select 'A004','赵明'
insert newsuserlist
select 101,'A001,A003' union
select 103,'A004,A006' union
select 105,'A001' union
select 150,'A001,A002'
--创建触发器
if exists(select 1 from sysobjects where id=object_id('tr') and xtype='TR')
drop trigger tr
go
create trigger tr
on userlist
for update
as
if update(userid) and not update(ID)
begin
update newsuserlist
set userlist=reverse(stuff(reverse(stuff(replace(','+A.userlist+',',','+D.userid+',',','+I.userid+','),1,1,'')),1,1,''))
from newsuserlist A
join deleted D on charindex(','+D.userid+',',','+A.userlist+',')>0
join inserted I on D.ID=I.ID
end
go
--测试
update userlist set userid='BBBB' where userid='A001'