高分求触发器,高手帮忙
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
请帮帮忙,,急
问题点数：100、回复次数：13Top
1 楼zjcxc（邹建）回复于 2005-09-22 16:47:20 得分 0
-- 插入处理触发器
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
Top
2 楼zjcxc（邹建）回复于 2005-09-22 16:51:14 得分 10
-- 插入处理触发器
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 tbTop
3 楼comerliang（天地良心)(性欲被自己倒分倒没了，以后再也不敢倒分了，想倒分的兄弟看看我的下场吧，男人没了性欲真不爽）回复于 2005-09-22 16:52:14 得分 0
老大就是强Top
4 楼xwqjamky（陌上烟花）回复于 2005-09-22 16:54:42 得分 0
谢谢邹老大Top
5 楼vivianfdlpw（）回复于 2005-09-22 16:56:10 得分 0
--禁用触发器递归
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
endTop
6 楼wgsasd311（自强不息）回复于 2005-09-22 16:57:00 得分 0
学习：）Top
7 楼libin_ftsafe（子陌红尘：TS for Banking Card）回复于 2005-09-22 16:57:04 得分 20
--创建表
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 TTop
8 楼zjcxc（邹建）回复于 2005-09-22 17:04:34 得分 50
-- 测试表
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 行）
--*/Top
9 楼lovcal（枫兮）回复于 2005-09-22 17:24:27 得分 0
都是牛人，学习Top
10 楼kinglh（用Delphi想BCB）回复于 2005-09-22 17:27:35 得分 0
来学习的Top
11 楼xwqjamky（陌上烟花）回复于 2005-09-22 17:28:05 得分 0
己完工Top
12 楼vivianfdlpw（）回复于 2005-09-22 17:33:57 得分 20
--创建表
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
Top
13 楼lcooc（don't make me think）回复于 2005-11-17 08:38:39 得分 0
up,学习Top
