高分求触发器,高手帮忙

[xwqjamky]

1,向Tid字段中有插入数据,有相同数据,那么另外一个字段IIid自动实现第一条数据为1,第二条为2,第三条为3..按此类推

2,当删除第2条记录时,那么IIid字段自动为1,2

如下表:
Tid IIid
aa 1
aa 2
bb 1
bb 2
cc 1
aa 3
bb 3
cc 2

当删除 Tid=3 and IIid=1
Tid=aa and IIid=1

那么select 后显示

Tid IIid
aa 1
bb 1
bb 2
aa 2
bb 3
cc 1


请帮帮忙,,急

[zjcxc]

-- 插入处理触发器
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

[zjcxc]

-- 插入处理触发器
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

[Comer]

老大就是强

[xwqjamky]

谢谢邹老大

[vivianfdlpw]

--禁用触发器递归
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

[wgsasd311]

学习：）

[子陌红尘]

--创建表
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

[zjcxc]

-- 测试表
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 tid

insert 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=1

select * 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 行）
--*/

[lovcal]

都是牛人，学习

[kinglh]

来学习的

[xwqjamky]

己完工

[vivianfdlpw]

--创建表
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 t

end
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

[lcooc]

up,学习