删除后递增-触发器？

__lhy 2011-07-07 04:11:15

一个表有一个递增的字段，以下有6条记录
0
1
2
3
4
5
当我删除第三条记录时（即2这条记录）怎么让后面紧跟的记录字段（3、4、5）分配递减1
即变成
0
1
2
3
4

...全文

102 16 打赏收藏转发到动态举报

写回复

用AI写文章

16 条回复

切换为时间正序

请发表友善的回复…

发表回复

xiangyehpu 2011-07-07

打赏
举报

[Quote=引用 12 楼 fredrickhu 的回复:]

Python code
利用触发器实现标识列连续。(支持批量插入)
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ttt]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ttt]
GO

/****** Object:……
[/Quote]

学习下

mingpei0703 2011-07-07

打赏
举报

[Quote=引用 13 楼 qq752923276 的回复:]
引用 5 楼 cd731107 的回复:

create TRIGGER [TR_tb_Delete] ON dbo.tb
FOR DELETE
AS
begin

update tbb set id=id-1
where id<(select id from deleted)
6#，7#更正过了的

end

谢谢 where id>(select id from deleted) 这个地方应……
[/Quote]

__lhy 2011-07-07

打赏
举报

哇太谢谢各位了每次来都这么快得到回复

我要散分！

__lhy 2011-07-07

打赏
举报

[Quote=引用 5 楼 cd731107 的回复:]

create TRIGGER [TR_tb_Delete] ON dbo.tb
FOR DELETE
AS
begin

update tbb set id=id-1
where id<(select id from deleted)

end
[/Quote]
谢谢 where id>(select id from deleted) 这个地方应该是大于吧

--小F-- 2011-07-07

打赏
举报

利用触发器实现标识列连续。(支持批量插入) 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ttt]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[ttt]

GO



/****** Object:  Table [dbo].[ttt]    Script Date: 2008-12-15 17:11:26 ******/

CREATE TABLE [dbo].[ttt] (

    [id] [int] IDENTITY (1, 1) NOT NULL ,

    [name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,

    [time] [datetime] NULL 

) ON [PRIMARY]

GO



ALTER TABLE [dbo].[ttt] ADD 

    CONSTRAINT [PK_ttt] PRIMARY KEY  CLUSTERED 

    (

        [id]

    )  ON [PRIMARY] 

GO



insert into ttt(name,time) values('logan',getdate());

insert into ttt(name,time) values('peter',getdate());

insert into ttt(name,time) values('man',getdate());

insert into ttt(name,time) values('lida',getdate());

insert into ttt(name,time) values('fcuandy',getdate());



select * from ttt

/*

1   logan   2008-12-15 17:36:37.780

2   peter   2008-12-15 17:36:37.780

3   man 2008-12-15 17:36:37.780

4   lida    2008-12-15 17:36:37.780

5   fcuandy 2008-12-15 17:36:37.793

*/





GO

CREATE TRIGGER tr ON ttt

INSTEAD OF INSERT

AS

    SET IDENTITY_INSERT ttt ON

    DECLARE @n INT

    SELECT @n=MAX(id) FROM ttt

    ;WITH fc AS

    (

        SELECT n=1

        UNION ALL

        SELECT nn=n+1 FROM fc WHERE n<@n

    ),fc1 AS

    (

    SELECT n FROM fc a

    LEFT JOIN ttt b

        ON a.n = b.id

        WHERE b.id IS NULL

    )

    INSERT ttt(id,name,time) SELECT n,name,time

        FROM

        (SELECT ROW_NUMBER() OVER (ORDER BY GETDATE()) idx,n FROM fc1) a

    INNER JOIN

        (

        SELECT idx=ROW_NUMBER() OVER (ORDER BY GETDATE()),name,time FROM inserted

        ) b

        ON a.idx=b.idx

    DECLARE @r INT

    SELECT @r=@@ROWCOUNT

    

    SET IDENTITY_INSERT ttt OFF

        INSERT ttt(name,time) SELECT name,time FROM 

            (

                SELECT idx=ROW_NUMBER() OVER (ORDER BY GETDATE()),name,time FROM inserted

            ) x

            WHERE idx>@r

    



GO

DELETE FROM ttt WHERE name = 'peter' OR name='lida'

GO

INSERT ttt SELECT 'xxx',getdate()

INSERT ttt SELECT 'yyy',GETDATE()

GO

SELECT * FROM ttt

/*

1   logan   2008-12-15 17:37:20.967

2   xxx 2008-12-15 17:37:21.013

3   man 2008-12-15 17:37:20.967

4   yyy 2008-12-15 17:37:21.030

5   fcuandy 2008-12-15 17:37:20.967

*/



DELETE FROM ttt WHERE name ='xxx' OR name='yyy'



INSERT ttt SELECT 'roy_88',GETDATE() UNION ALL SELECT 'limpire',GETDATE() UNION ALL SELECT '熊',GETDATE()



SELECT * FROM ttt



/*

1   logan   2008-12-15 17:38:29.450

2   roy_88  2008-12-15 17:38:29.530

3   man 2008-12-15 17:38:29.467

4   limpire 2008-12-15 17:38:29.530

5   fcuandy 2008-12-15 17:38:29.467

6   熊   2008-12-15 17:38:29.530

*/





本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/fcuandy/archive/2008/12/15/3522876.aspx

AcHerat 2011-07-07

打赏
举报

[Quote=引用 8 楼 zy112429 的回复:]

引用 5 楼 cd731107 的回复:
create TRIGGER [TR_tb_Delete] ON dbo.tb
FOR DELETE
AS
begin

update tbb set id=id-1
where id<(select id from deleted)

end
就是这个
[/Quote]

啥时候升星的？

Mr_Nice 2011-07-07

打赏
举报

[Quote=引用 3 楼 acherat 的回复:]

引用楼主 qq752923276 的回复:
一个表有一个递增的字段，以下有6条记录
0
1
2
3
4
5
当我删除第三条记录时（即2这条记录）怎么让后面紧跟的记录字段（3、4、5）分配递减1
即变成
0
1
2
3
4

楼主修改自增列是没有意义的，何况是不能更新的，除非是普通列。

建议楼主查询的时候自己做个排序的标识列就是了。何必用触发器这么麻烦。
……
[/Quote]

查询中直接做一个排序列即可。
更新，消耗资源过大，而且效果也未必好。删除的标示值也在表中说明了一些问题。
比如误删除之类的...

AcHerat 2011-07-07

打赏
举报



create TRIGGER [TR_tb_Delete] ON dbo.tb  

FOR DELETE

AS

begin	

declare @i int

set @i = 0

update tbb 

set id=@i,@i=@i+1  

end

go

GoAwayZ 2011-07-07

打赏
举报

[Quote=引用 5 楼 cd731107 的回复:]
create TRIGGER [TR_tb_Delete] ON dbo.tb
FOR DELETE
AS
begin

update tbb set id=id-1
where id<(select id from deleted)

end
[/Quote]就是这个

cd731107 2011-07-07

打赏
举报

create TRIGGER [TR_tb_Delete] ON dbo.tb
FOR DELETE
AS
begin

update tb set id=id-1
where id>(select id from deleted)

end

cd731107 2011-07-07

打赏
举报

--反了，用这个
create TRIGGER [TR_tb_Delete] ON dbo.tb
FOR DELETE
AS
begin

update tbb set id=id-1
where id>(select id from deleted)

end

cd731107 2011-07-07

打赏
举报

create TRIGGER [TR_tb_Delete] ON dbo.tb
FOR DELETE
AS
begin

update tbb set id=id-1
where id<(select id from deleted)

end

__lhy 2011-07-07

打赏
举报

[Quote=引用 2 楼 zy112429 的回复:]

递增字段无法更新
[/Quote]
这个字段没有在数据表中设置递增是程序上面的控制

AcHerat 2011-07-07

打赏
举报

[Quote=引用楼主 qq752923276 的回复:]
一个表有一个递增的字段，以下有6条记录
0
1
2
3
4
5
当我删除第三条记录时（即2这条记录）怎么让后面紧跟的记录字段（3、4、5）分配递减1
即变成
0
1
2
3
4
[/Quote]

楼主修改自增列是没有意义的，何况是不能更新的，除非是普通列。

建议楼主查询的时候自己做个排序的标识列就是了。何必用触发器这么麻烦。

GoAwayZ 2011-07-07