CREATE TRIGGER UPDATE_ ON Table1 FOR INSERT,UPDATE,DELETE AS UPDATE Table2 SET Table1_Id =(??) WHERE Table1_Id = (SELECT Table1_Id FROM INSERTED) UPDATE Table2 SET Table1_Id =(??) WHERE Table1_Id = (SELECT Table1_Id FROM DELETED)
drop table ta drop table tbcreate table ta (id int,name varchar(20)) insert into ta select 1,'aa' insert into ta select 2,'bb' insert into ta select 3,'cc'create table tb(id int,table1_id varchar(50)) insert into tb select 1,'1,2,3' insert into tb select 2,'2,3' insert into tb select 3,'1,3'create trigger up_de_tb on ta for update ,delete as begin if (select count(1) from inserted)>0 and (select count(1) from deleted)>0 begin update b set table1_id=replace(','+table1_id+',',','+ltrim(d.id)+',',','+ltrim(i.id)+',') from tb b,deleted d,inserted i where d.name=i.name and charindex(','+ltrim(d.id)+',',','+table1_id+',')>0update b set table1_id=case when left(table1_id,1)=',' then stuff(table1_id,1,1,'') else table1_id end from tb b update b set table1_id=case when right(table1_id,1)=',' then stuff(table1_id,len(table1_id),1,'') else table1_id end from tb b endendupdate ta set id=5 where name='aa'select * from tb 1 5,2,3 2 2,3 3 5,3
CREATE TRIGGER UPDATE_
ON Table1
FOR INSERT,UPDATE,DELETE
AS
UPDATE Table2 SET Table1_Id =(??) WHERE Table1_Id = (SELECT Table1_Id FROM INSERTED)
UPDATE Table2 SET Table1_Id =(??) WHERE Table1_Id = (SELECT Table1_Id FROM DELETED)
drop table tbcreate table ta (id int,name varchar(20))
insert into ta select 1,'aa'
insert into ta select 2,'bb'
insert into ta select 3,'cc'create table tb(id int,table1_id varchar(50))
insert into tb select 1,'1,2,3'
insert into tb select 2,'2,3'
insert into tb select 3,'1,3'create trigger up_de_tb
on ta
for update ,delete
as
begin
if (select count(1) from inserted)>0 and (select count(1) from deleted)>0
begin
update b set table1_id=replace(','+table1_id+',',','+ltrim(d.id)+',',','+ltrim(i.id)+',') from tb b,deleted d,inserted i
where d.name=i.name and charindex(','+ltrim(d.id)+',',','+table1_id+',')>0update b set table1_id=case when left(table1_id,1)=',' then stuff(table1_id,1,1,'') else table1_id end from tb b
update b set table1_id=case when right(table1_id,1)=',' then stuff(table1_id,len(table1_id),1,'') else table1_id end from tb b
endendupdate ta set id=5 where name='aa'select * from tb
1 5,2,3
2 2,3
3 5,3