-- 插入处理触发器
create trigger tr_insert on tb
instead of insert
as
select Tid, IIid=identity(int,1,1) into #
from(
select top 100 percent tid from inserted order by tid)a
insert tb(tid,IIid)
select a.Tid, a.IIid+isnull(b.IIid,0)
from # a
left join(
select Tid, IIid=max(IIid) from tb a
where exists(
select * from # where Tid=a.Tid)
group by Tid
)b on a.Tid=b.Tid
create trigger tr_insert on tb
instead of insert
as
select Tid, IIid=identity(int,1,1) into #
from(
select top 100 percent tid from inserted order by tid)a
insert tb(tid,IIid)
select a.Tid, a.IIid+isnull(b.IIid,0)
from # a
left join(
select Tid, IIid=max(IIid) from tb a
where exists(
select * from # where Tid=a.Tid)
group by Tid
)b on a.Tid=b.Tid
create trigger tr_insert on tb
instead of insert
as
select Tid, IIid=identity(int,1,1) into #
from(
select top 100 percent tid from inserted order by tid)a
insert tb(tid,IIid)
select a.Tid, a.IIid-a1.IIid+isnull(b.IIid,0)
from # a
inner join(
select tid, IIid=min(IIid)-1 from #
group by tid
)a1 on a.tid=a1.tid
left join(
select Tid, IIid=max(IIid) from tb a
where exists(
select * from # where Tid=a.Tid)
group by Tid
)b on a.Tid=b.Tid
go-- 删除处理触发器
create trigger tr_delete on tb
for delete
as
declare @IIid int,@Tid varchar(8000)
declare tb cursor local
for
select distinct tid from deleted
open tb
fetch tb into @tid
while @@fetch_status=0
begin
set @IIid=0
update tb set @IIid=@IIid+1,IIid=@IIid
where tid=@tid
fetch tb into @tid
end
close tb
deallocate tb
exec sp_configure 'nested triggers','0'
reconfigure with override
go--创建触发器
create triggrt tr
on 表
for insert,delete
as
declare @tb table
(
ID int identity,
Tid varchar(10),
IIid int
)
--插入
if exists(select 1 from inserted)
begin
insert @tb(Tid,IIid) select * from inserted
insert 表
select Tid
,(select max(IIid) from 表 where Tid=t.Tid)+ID
from @tb t
end
else
begin --删除
insert @tb(Tid,IIid)
select * from 表 t
where exists(select 1 from deleted where Tid=t.Tid)
delete from 表 t where exists(select 1 from deleted where Tid=t.Tid)
insert 表
select * from @tb
end
create table T(Tid varchar(10),IIid int)
go--创建触发器
create trigger trg_t
on t
for insert,delete
as
begin
update a set IIid = (select count(*) from t where Tid=a.Tid) from t a where a.IIid is null
update a set IIid = a.IIid - 1 from t a,deleted b where a.Tid=b.Tid and a.IIid > b.IIid
end
go--插入数据
insert into T(Tid) select 'aa'
insert into T(Tid) select 'aa'
insert into T(Tid) select 'bb'
insert into T(Tid) select 'bb'
insert into T(Tid) select 'cc'
insert into T(Tid) select 'aa'
insert into T(Tid) select 'bb'
insert into T(Tid) select 'cc'--查看结果
select * from T
/*
Tid IIid
----- ----
aa 1
aa 2
bb 1
bb 2
cc 1
aa 3
bb 3
cc 2
*/--删除数据
delete t where Tid='cc' and IIid=1
delete t where Tid='aa' and IIid=1
--查看结果
select * from T
/*
Tid IIid
----- ----
aa 1
bb 1
bb 2
aa 2
bb 3
cc 1
*/--删除测试数据
drop trigger trg_t
drop table T
create table tb(tid varchar(10),IIid int)
go-- 插入处理触发器
create trigger tr_insert on tb
instead of insert
as
select Tid, sid=identity(int,1,1) into #1 from inserted
select Tid, IIid=identity(int,1,1), sid=sid*1 into #
from(select top 100 percent * from #1)a
order by tidinsert tb(tid,IIid)
select a.Tid, a.IIid-a1.IIid+isnull(b.IIid,0)
from # a
inner join #1 aa on a.sid=aa.sid
inner join(
select tid, IIid=min(IIid)-1 from #
group by tid
)a1 on a.tid=a1.tid
left join(
select Tid, IIid=max(IIid) from tb a
where exists(
select * from # where Tid=a.Tid)
group by Tid
)b on a.Tid=b.Tid
order by aa.sid
go-- 删除处理触发器
create trigger tr_delete on tb
for delete
as
declare @IIid int,@Tid varchar(8000)
declare tb cursor local
for
select distinct tid from deleted
open tb
fetch tb into @tid
while @@fetch_status=0
begin
set @IIid=0
update tb set @IIid=@IIid+1,IIid=@IIid
where tid=@tid
fetch tb into @tid
end
close tb
deallocate tb
go-- 插入数据, 测试插入触发顺
insert tb select 'aa',1
union all select 'aa',2
union all select 'bb',1
union all select 'bb',2
union all select 'cc',1
union all select 'aa',3
union all select 'bb',3
union all select 'cc',2
select * from tb
go-- 删除数据, 测试删除触发器
delete from tb
where tid='cc' and IIid=1
delete from tb
where tid='aa' and IIid=1select * from tb
go-- 删除测试
drop table tb/*-- 结果
tid IIid
---------- -----------
aa 1
aa 2
bb 2
bb 3
cc 1
aa 3
bb 1
cc 2(所影响的行数为 8 行)
tid IIid
---------- -----------
aa 1
bb 2
bb 3
aa 2
bb 1
cc 1(所影响的行数为 6 行)
--*/
create table tb(Tid varchar(10),IIid int)
go--禁用触发器递归
exec sp_configure 'nested triggers','0'
reconfigure with override
go
--创建触发器
create trigger tr_tb
on tb
for insert,delete
as
declare @tb table
(
ID int identity,
Tid varchar(10),
IIid int
)
--插入
if exists(select 1 from inserted)
begin
insert @tb(Tid,IIid) select * from inserted
delete tb from tb t where exists(select 1 from inserted where Tid=t.Tid)
insert tb
select Tid
,isnull((select max(IIid) from tb where Tid=t.Tid),0)+
(select count(1) from @tb where Tid=t.Tid and ID<=t.ID)
from @tb tend
else
begin --删除
insert @tb(Tid,IIid)
select * from tb t
where exists(select 1 from deleted where Tid=t.Tid)
delete tb from tb t where exists(select 1 from deleted where Tid=t.Tid)
insert tb
select Tid,(select count(1) from @tb where Tid=t.Tid and ID<=t.ID) from @tb t
end
go-- 插入数据, 测试插入触发顺
insert tb select 'aa',1
union all select 'aa',2
union all select 'bb',1
union all select 'bb',2
union all select 'cc',1
union all select 'aa',3
union all select 'bb',3
union all select 'cc',2
select * from tb
go
----查看结果
select * from tb/*
Tid IIid
---------- -----------
aa 1
aa 2
bb 1
bb 2
cc 1
aa 3
bb 3
cc 2(所影响的行数为 8 行)
*/--删除数据
delete tb where Tid='cc' and IIid=1
delete tb where Tid='aa' and IIid=1
--查看结果
select * from tb/*
Tid IIid
---------- -----------
aa 1
aa 2
bb 1
bb 2
cc 1
bb 3(所影响的行数为 6 行)
*/
--删除测试环境
drop table tb