drop table T
go
create table T(id varchar ( 10 ) primary key ,name varchar ( 10 ))
go
-- 用触发器完成
create trigger tr_T_insert on T
instead of insert
as
begin
declare @i int
select * into # from inserted
select @i = isnull ( max (id), 1000000 ) from T
update # set id = right ( 1000000 + @i , 6 ), @i = @i + 1
insert T select * from #
end
go
-- 测试
insert T
select 1 , ' a ' union all -- 这里1为任何值都不影响id插入的值
select 1 , ' b '
select * from T
/*
id name
---------- ----------
000001 a
000002 b
(所影响的行数为 2 行)
*/
go
if object_id ( ' T ' ) is not null
drop table T
go
create table T(id char ( 6 ),name nvarchar ( 10 ))
go
-- 定义一个函数
if object_id ( ' test_fun ' ) is not null
drop function test_fun
go
create function test_fun()
returns char ( 6 )
as
begin
declare @Max char ( 6 )
select @Max = right ( ' 000000 ' + rtrim (( isnull ( max (ID), 0 ) + 1 )), 6 ) from T
return @Max
end
go
alter table T add constraint UQ_T_default default dbo.test_fun() for ID
go
insert T(Name) values ( ' AA ' )
insert T(Name) values ( ' BB ' )
-- 或
insert T values (dbo.test_fun(), ' CC ' )
go
select * from T
go
/*
id name
------ ----------
000001 AA
000002 BB
000003 CC
(所影响的行数为 3 行)
*/
drop table T
drop function test_fun
go
<!-- [if gte mso 9]><![endif]--><!-- [if gte mso 9]><![endif]--><!-- [if gte mso 10]>
-- 用數據庫觸發器 ( 處理自增列斷號 )
if object_id('T') is not null
drop table T
go
create table T(id INT IDENTITY,name nvarchar(10))
go
CREATE TRIGGER Tr_dT ON T
INSTEAD OF DELETE
AS
SET NOCOUNT ON ;
BEGIN
DECLARE @MinID INT
SELECT @MinID=MIN(ID)-1 FROM DELETED
DELETE T WHERE ID IN(SELECT ID FROM deleted)
SELECT ID=ID*1,NAME INTO # FROM T WHERE ID>@MinID
DELETE T WHERE ID>@MinID
UPDATE # SET ID=@MinID,@MinID=@MinID+1
SET IDENTITY_INSERT ON ;
INSERT INTO T(ID,Name)SELECT ID,Name FROM #
SET IDENTITY_INSERT OFF;
DBCC CHECKIDENT('T',RESEED,@MinID)
DROP TABLE #
END
go
--新增數據
insert T(Name) values('AA')
insert T(Name) values('BB')
insert T(Name) values('CC')
insert T(Name) values('DD')
insert T(Name) values('EE')
GO
SELECT * FROM T
/*
id name
1 AA
2 BB
3 CC
4 DD
5 EE
*/
go
--測刪除
DELETE T WHERE ID IN(2,3)
go
insert T(Name) values('FF')
SELECT * FROM T
/*
id name
1 AA
2 DD
3 EE
4 FF
*/
go
drop table T