inserted、deleted
这是两个虚拟表,inserted 保存的是 insert 或 update 之后所影响的记录形成的表,deleted 保存的是 delete 或 update 之前所影响的记录形成的表。http://www.cftea.com/c/761.asp
这是两个虚拟表,inserted 保存的是 insert 或 update 之后所影响的记录形成的表,deleted 保存的是 delete 或 update 之前所影响的记录形成的表。http://www.cftea.com/c/761.asp
go
create table [ta]([id] int,[childrenstr] varchar(20))
insert [ta]
select 1,'1,2' union all
select 2,'3' union all
select 3,'4,5'
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[classid] int)
insert [tb]
select 1,1 union all
select 2,1 union all
select 3,2 union all
select 4,3 union all
select 5,3select * from [ta]
select * from [tb]
go
create trigger tri_tb on tb
for insert,update,delete
as
if not exists(select 1 from deleted) --insert
begin
update ta
set childrenstr=childrenstr+','+rtrim(i.id)
from inserted i
where ta.id=i.classid
end
else if not exists(select 1 from inserted) --delete
begin
update ta
set childrenstr=stuff(replace(','+childrenstr,','+rtrim(d.id),''),1,1,'')
from deleted d
where ta.id=i.classid
end
else --update
begin
update ta
set childrenstr=stuff(replace(','+childrenstr,','+rtrim(d.id),''),1,1,'')
from deleted d
where ta.id=i.classid update ta
set childrenstr=childrenstr+','+rtrim(i.id)
from inserted i
where ta.id=i.classid
end
go
insert: inserted
delete: deleteddeleted放老数据,inserted放新数据deleted,inserted逻辑表的结构与你建触发器的表相同。
比如 ta,tb以id关联,删除ta 记录时,删除tb关联记录,可写作 :
create trigger t on ta for delete
as
delete b from tb b inner join deleted a on a.id=b.id --这里deleted就代表了ta中被删除的数据
create trigger tri_tb on tb
for insert,update,delete
as
if not exists(select 1 from deleted) --insert
begin
update ta
set childrenstr=childrenstr+','+rtrim(i.id)
from inserted i
where ta.id=i.classid
end
else if not exists(select 1 from inserted) --delete
begin
update ta
set childrenstr=stuff(replace(','+childrenstr,','+rtrim(d.id),''),1,1,'')
from deleted d
where ta.id=d.classid
end
else --update
begin
update ta
set childrenstr=stuff(replace(','+childrenstr,','+rtrim(d.id),''),1,1,'')
from deleted d
where ta.id=d.classid update ta
set childrenstr=childrenstr+','+rtrim(i.id)
from inserted i
where ta.id=i.classid
end
go
updateed.id
deleted.id
go
create table [ta]([id] int,[childrenstr] varchar(20))
insert [ta]
select 1,'1,2' union all
select 2,'3' union all
select 3,'4,5'
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[classid] int)
insert [tb]
select 1,1 union all
select 2,1 union all
select 3,2 union all
select 4,3 union all
select 5,3select * from [ta]
select * from [tb]
go
create trigger tri_tb on tb
for insert,update,delete
as
if not exists(select 1 from deleted) --insert
begin
update ta
set childrenstr=childrenstr+','+rtrim(i.id)
from inserted i
where ta.id=i.classid
end
else if not exists(select 1 from inserted) --delete
begin
update ta
set childrenstr=stuff(replace(','+childrenstr,','+rtrim(d.id),''),1,1,'')
from deleted d
where ta.id=d.classid
end
else --update
begin
update ta
set childrenstr=stuff(replace(','+childrenstr,','+rtrim(d.id),''),1,1,'')
from deleted d
where ta.id=d.classid update ta
set childrenstr=childrenstr+','+rtrim(i.id)
from inserted i
where ta.id=i.classid
end
goinsert tb values(6,1)
select * from [ta]
/*
1 1,2,6
2 3
3 4,5
*/update tb set classid=2 where id=2
select * from [ta]
/*
1 1,6
2 3,2
3 4,5
*/
delete tb where id=4
select * from [ta]
/*
1 1,6
2 3,2
3 5
*/
if object_id('[ta]') is not null drop table [ta]
go
create table [ta]([id] int,[childrenstr] varchar(20))
insert [ta]
select 1,'1,2' union all
select 2,'3' union all
select 3,'4,5'
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[classid] int)
insert [tb]
select 1,1 union all
select 2,1 union all
select 3,2 union all
select 4,3 union all
select 5,3select * from [ta]
select * from [tb]
go
create trigger tri_tb on tb
for insert,update,delete
as
if exists(select 1 from deleted) --update or delete
begin
update ta
set childrenstr=stuff(replace(','+childrenstr,','+rtrim(d.id),''),1,1,'')
from deleted d
where ta.id=d.classid
end
if exists(select 1 from inserted) --update or insert
begin
update ta
set childrenstr=childrenstr+','+rtrim(i.id)
from inserted i
where ta.id=i.classid
end
goinsert tb values(6,1)
select * from [ta]
/*
1 1,2,6
2 3
3 4,5
*/update tb set classid=2 where id=2
select * from [ta]
/*
1 1,6
2 3,2
3 4,5
*/
delete tb where id=4
select * from [ta]
/*
1 1,6
2 3,2
3 5
*/
insert tb select 6,1 union all select 7,1 union all select 8,2
select * from [ta]
/*
1 1,2,7
2 3,8
3 4,5
*/