有这么两个表
user
id username
1 bryan
2 tony
.....role
id userid
1 1,2,6,7,8
2 1,3,5,6,7,9
.....
如果我把user表的id为1的用户删除了,如何将role表的userid字段中含有1的条目去掉1如删除user表id为1的数据,那么role表第一条数据将变成2,6,7,8 该如何写sql语句?在线等
user
id username
1 bryan
2 tony
.....role
id userid
1 1,2,6,7,8
2 1,3,5,6,7,9
.....
如果我把user表的id为1的用户删除了,如何将role表的userid字段中含有1的条目去掉1如删除user表id为1的数据,那么role表第一条数据将变成2,6,7,8 该如何写sql语句?在线等
AS
BEGIN
UPDATE [ROLE] SET [userid ] =REPALACE([userid ],LTRIM(D.ID)+',','') FROM DELETED D WHERE D.ID= [ROLE].ID
END?[/code]??
CREATE TRIGGER TRI ON [USER] FOR DELETE
AS
BEGIN
UPDATE [ROLE] SET [userid ] =REPLACE([userid ],LTRIM(D.ID)+',','') FROM DELETED D WHERE D.ID= [ROLE].ID
END
AS
update role set userid = substring(replace(',' + role.userid + ',', ',' + d.id + ',', ','), 2, len(replace(',' + role.userid + ',', ',' + d.id + ',', ',')) - 2)
from role join deleted d on charindex(d.id, role.userid) > 0
create table [user](id varchar(12),username varchar(12))
insert [user] select
'1' , 'bryan'union all select
'2' , 'tony'union all select
'3', 'szy' create table role(id int,userid varchar(20))
insert role select
1 , '1,2,6,7,8' union all select
2 , '1,3,5,6,7,9'
go
create trigger gg on [user]
for deleteas
if exists(select count(1) from deleted)
begin
update role
set [userid]=(case when patindex('%'+d.id+'%',[userid])=len(userid)
then replace(userid,','+d.id,'')
else replace(userid,d.id+',','')
end)
from deleted d
where charindex(d.id,userid,1)>0
end
go
delete from [user] where id='3'
select * from roledrop table [user]
drop table [role]id userid
----------- --------------------
1 1,2,6,7,8
2 1,5,6,7,9(2 行受影响)
create table [user](id varchar(12),username varchar(12))
insert [user] select
'1' , 'bryan'union all select
'2' , 'tony'union all select
'3', 'szy'union all select
'4','aa' union all select
'9','bb'create table role(id int,userid varchar(20))
insert role select
1 , '1,2,6,7,8' union all select
2 , '1,3,5,6,7,9'
go
create trigger gg on [user]
for deleteas
if exists(select count(1) from deleted)
begin
update role
set [userid]=(case when patindex('%'+d.id+'%',[userid])=len(userid)
then replace(userid,','+d.id,'')
else replace(userid,d.id+',','')
end)
from deleted d
where charindex(d.id,userid,1)>0
end
go /*
id userid
----------- --------------------
1 1,2,6,8
2 1,3,5,6,9(2 行受影响)
*/-------------------------------------------------------delete from [user] where id='1' --删除最前面的id
select * from role
/*
id userid
----------- --------------------
1 2,6,7,8
2 3,5,6,7,9
*/
delete from [user] where id='9' --删除最后面的id
select * from role
----------- --------------------
/*
1 1,2,6,7,8
2 1,3,5,6,7(2 行受影响)
*/
drop table [user]
drop table [role]
d是触发器里 deleted 表的别名
id 是你删除[user]表里那条记录的id
d 是触发器里 deleted 表的别名
id 是你删除[user]表里那条记录的id
AS
BEGIN
UPDATE [ROLE] SET [userid ] =REPLACE(','+LTRIM([userid ])+',',','+LTRIM(D.ID)+',','') FROM DELETED D WHERE D.ID= [ROLE].ID
END
这样不会出现小卒的问题
77,你这个能解决我的BUG,但是你的还要再动一下比如:“,1,2,4,6,11,“ 要把中间的4去掉,按照你的 ','+LTRIM(D.ID)+',' 就是 ,4, 那就变成
,1,26,11, 错了
as
begin
declare @a int
declare cur_a cursor for select id from deleted
open cur_a
fetch next from cur_a into @a
while @@fetch_status=0
begin if exists(select 1 from [role] where charindex(cast(@a as varchar)+',',userid)>0)
update [role] set userid=replace(userid,cast(@a as varchar)+',','')
if exists(select 1 from [role] where charindex(cast(@a as varchar),userid)>0)
update [role] set userid=replace(userid,@a,'')
set @a=-1 fetch next from cur_a into @a
end
close cur_a
deallocate cur_aend
/*
id userid
----------- -------------------- 原始记录
1 1,2,6,7,8,11,13
2 1,3,5,6,7,9,11
*/delete from [user] where id='11' --删除最后面的id
select * from roleid userid
----------- --------------------
1 1,2,6,7,8,13
2 1,3,5,6,7,9,
delete from [user] where id='1' --删除最前面的id
select * from roleid userid
----------- --------------------
1 2,6,7,8,3
2 3,5,6,7,9,
delete from [user] where id='11' --删除中间的id
select * from roleid userid
----------- --------------------
1 1,2,6,7,8,13
2 1,3,5,6,7,9,
-- 为什么我测试楼上的总多一个逗号啊