--创建测试环境 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'select * from userlist select * from newsuserlist--删除测试环境 drop table userlist,newsuserlist--结果 /*ID userid username ----------- -------------------- -------------------- 2 A002 李四 3 A003 王五 4 A004 赵明 1 BBBB 张三(所影响的行数为 4 行)id userlist ----------- ------------------------------- 101 BBBB,A003 103 A004,A006 105 BBBB 150 BBBB,A002(所影响的行数为 4 行) */
不知道是不是符合你的要求:--建表 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 goupdate userlist set username='王五1' where userid=3select * from userlist select * from newsuserlistdrop trigger tr_updatename drop table userlist,newsuserlist
对不起啊,大伙!我复制错了数据,请原谅!!! 我有两张表userlist内容如下: userid 主键 usernamenewsuserlist内容如下: id '主键 userid这两个表只有userid一列相同我现在想在userlist表上建立一个UPDATE触发器,使userlist中userid字段更新时,newsuserlist表中的等值userid也相应更新 不过,一个朋友给出了答案,我试了可以!!我把代码贴出来!!谢谢大家的热心,我还是会给大家分的:) 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
我追问一句:为什么这样可以 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 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'select * from userlist
select * from newsuserlist--删除测试环境
drop table userlist,newsuserlist--结果
/*ID userid username
----------- -------------------- --------------------
2 A002 李四
3 A003 王五
4 A004 赵明
1 BBBB 张三(所影响的行数为 4 行)id userlist
----------- -------------------------------
101 BBBB,A003
103 A004,A006
105 BBBB
150 BBBB,A002(所影响的行数为 4 行)
*/
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
goupdate userlist set username='王五1'
where userid=3select * from userlist
select * from newsuserlistdrop trigger tr_updatename
drop table userlist,newsuserlist
我有两张表userlist内容如下:
userid 主键
usernamenewsuserlist内容如下:
id '主键
userid这两个表只有userid一列相同我现在想在userlist表上建立一个UPDATE触发器,使userlist中userid字段更新时,newsuserlist表中的等值userid也相应更新
不过,一个朋友给出了答案,我试了可以!!我把代码贴出来!!谢谢大家的热心,我还是会给大家分的:)
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
--newsuserlist中设置userid外键引用userlist中的userid并设置on update cascade就可以了
级联 昨天刚看过:)
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
为什么一定要用变量过度一下?