这个要用触发器啊。create trigger test
on b
for delete
as
begin
update a set mm=stuff(replace(';'+mm,';'+b.名称,''),1,1,'')
from deleted as b join a
on a.id =b.id
where exists (select 2 from deleted where deleted.id=a.id)
end
on b
for delete
as
begin
update a set mm=stuff(replace(';'+mm,';'+b.名称,''),1,1,'')
from deleted as b join a
on a.id =b.id
where exists (select 2 from deleted where deleted.id=a.id)
end
create trigger test
on b
for delete
as
begin
update a set mm=stuff(replace(','+mm,','+b.名称,''),1,1,'')
from deleted as b join a
on a.id =b.id
where exists (select 2 from deleted where deleted.id=a.id)
end
IF object_id('tgr_B_delete', 'TR') is not NULL
DROP trigger tgr_B_delete
GO
create trigger tgr_B_delete
on B
for delete
as
BEGIN
DECLARE @BID VARCHAR(10)
DECLARE @BINDEX VARCHAR(10)
DECLARE @T TABLE(ID INT,I INT)
INSERT INTO @T SELECT ID,ROW_NUMBER()OVER(ORDER BY GETDATE()) FROM DELETED
SELECT TOP 1 @BID=CAST(ID AS VARCHAR(10)),@BINDEX=I FROM @T ORDER BY I
WHILE @@ROWCOUNT<>0
BEGIN
UPDATE A
SET mm=STUFF(REPLACE(','+mm+',',','+@BID+',',','),1,1,'')
WHERE ','+ mm+',' LIKE '%,'+@BID+',%'
UPDATE A
SET mm=STUFF(mm,LEN(mm),1,'')
DELETE FROM @T WHERE I=@BINDEX
SELECT TOP 1 @BID=CAST(ID AS VARCHAR(10)),@BINDEX=I FROM @T ORDER BY I
END
END
GO
IF object_id('tgr_B_delete', 'TR') is not NULL
DROP trigger tgr_B_delete
GO
create trigger tgr_B_delete
on B
for delete
as
BEGIN
DECLARE @BID VARCHAR(10)
DECLARE @BINDEX VARCHAR(10)
DECLARE @T TABLE(ID INT,I INT)
INSERT INTO @T SELECT ID,ROW_NUMBER()OVER(ORDER BY GETDATE()) FROM DELETED
SELECT TOP 1 @BID=CAST(ID AS VARCHAR(10)),@BINDEX=I FROM @T ORDER BY I
WHILE @@ROWCOUNT<>0
BEGIN
UPDATE A
SET mm=STUFF(REPLACE(','+mm+',',','+@BID+',',','),1,1,'')
WHERE ','+ mm+',' LIKE '%,'+@BID+',%'
UPDATE A
SET mm=STUFF(mm,LEN(mm),1,'')
WHERE RIGHT(mm,1)=','
DELETE FROM @T WHERE I=@BINDEX
SELECT TOP 1 @BID=CAST(ID AS VARCHAR(10)),@BINDEX=I FROM @T ORDER BY I
END
END
GO
可以試一下declare @i int =1
update A表 set mm=substring(REPLACE(','+mm+',',','+cast(@i as varchar)+',',','),2,LEN(REPLACE(','+mm+',',','+cast(@i as varchar)+',',','))-2) where CHARINDEX(','+CAST(@i as varchar)+',','%,'+mm+',%')>0
把 mm 单独提出来做成 C表(aid,bid)