有一张数据表结构如下
create table user
(
ID int not null identity (1, 1);
name varchar(20)
)数据如下
ID name
1 张三
2 李四
3 王五
4 钱六
...————————————
现在删除(2,李四)
所有ID比李四大的数据行ID自减1,数据表中的数据变为
ID name
1 张三
2 王五
3 钱六
...
————————————
求具体的SQL触发器代码
create table user
(
ID int not null identity (1, 1);
name varchar(20)
)数据如下
ID name
1 张三
2 李四
3 王五
4 钱六
...————————————
现在删除(2,李四)
所有ID比李四大的数据行ID自减1,数据表中的数据变为
ID name
1 张三
2 王五
3 钱六
...
————————————
求具体的SQL触发器代码
--需要時候處理便是了,非要自增?
--2000
select ID=(select count(1)from tb where id<=t.id),
[Name]
from tb t
--2005
select ID=row_number()over(order by (select 1)),
[Name]
from tb t
--非要這樣等下面貼
if object_id('[tb]') is not null drop table [tb]
create table [tb]([ID] int,[name] varchar(4))
insert [tb]
select 1,'张三' union all
select 2,'李四' union all
select 3,'王五' union all
select 4,'钱六'
create trigger tri
on tb
for delete
as
begin
update tb set id =id-1 where id>(select id from deleted)
enddelete tb where id=2select * from [tb]/*
ID name
----------- ----
1 张三
2 王五
3 钱六(3 行受影响)
*/
(
ID int identity(1,1) primary key,
Name varchar(30)
)
insert into table1 select '张三'
insert into table1 select '李四'
insert into table1 select '王五'
insert into table1 select '钱六'create trigger TG_UpdateInfo on table1
for delete
as
begin
select Name,isnull((select ID-1 from table1 where ID>1 and K.ID=ID),1) as ID from table1 K
enddelete from table1 where ID=2
Name ID
------------------------------ -----------
张三 1
王五 2
钱六 3(3 行受影响)
看我10楼的
如果你确实要修改标识列请看下面的
--(4)identity列与普通列的相互转换
---<1>indetity列转换为普通列
create table #tg
(
id int identity(1,1),
col1 int
)
insert into #tg select 1
union all select 2
union all select 3
alter table #tg
add col2 int
update #tg set col2=id
select * from #tg
alter table #tg
drop column id
select * from #tg
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/ws_hgo/archive/2009/02/06/3866850.aspx
on tb
for delete
as
begin
update tb set id =id-(select count(*) from deleted a where a.id<=tb.id)
end可以应付多条删除
if object_id('[tb]') is not null drop table [tb]
create table [tb]([ID] int,[name] varchar(4))
insert [tb]
select 1,'张三' union all
select 2,'李四' union all
select 3,'王五' union all
select 4,'钱六'go
create trigger tri
on tb
for delete
as
begin
update tb set id =id-(select count(*) from deleted a where a.id<=tb.id)
end
godelete tb where id in(1,2,3)
go
select * from [tb](4 行受影响)(1 行受影响)(3 行受影响) ID name
----------- ----
1 钱六(1 行受影响)
-- Author : happyflystone
-- Date : 2010-03-11 14:36:50
-- Version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
--
-------------------------------------------------------------------------- Test Data: ta
IF OBJECT_ID('[ta]') IS NOT NULL
DROP TABLE [ta]
Go
CREATE TABLE ta([ID] INT identity,[name] NVARCHAR(2))
Go
INSERT INTO ta
SELECT '张三' UNION ALL
SELECT '李四' UNION ALL
SELECT '王五' UNION ALL
SELECT '钱六'
GO
create trigger tr_del
on ta
for delete
as
begin
declare @i varchar(10)
select top 1 @i = ltrim(id) from deleted
exec('SET IDENTITY_INSERT dbo.ta On
select (select count(1)+1 from ta where id < a.id) as id,name into # from ta a
truncate table ta
insert into ta(id,name) select * from #;
drop table #
SET IDENTITY_INSERT dbo.ta Off
')
end
go--Start
delete from ta where id = 2 or id = 3
go
SELECT
*
FROM
TA--Result:
/*
ID name
----------- ----
1 张三
2 钱六(2 行受影响)*/
--End