if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tr_student_insert]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[tr_student_insert]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tr_student_delete]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[tr_student_delete]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[student]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[student]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[studentmaxid]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[studentmaxid]
GO
CREATE TABLE [dbo].[student] (
[id] [int] NULL ,
[name] [char] (10) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[studentmaxid] (
[id] [int] NOT NULL ,
[dates] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE UNIQUE INDEX [name] ON [dbo].[student]([name]) ON [PRIMARY]
GO
CREATE INDEX [id] ON [dbo].[student]([id]) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER dbo.tr_student_insert ON [dbo].[student]
FOR INSERT
AS
set nocount on
declare @cnt int
select @cnt=count(*) from inserted
if @cnt=0
return
begin tran
declare @name char(10),@id int
--锁定 studentmaxid
update studentmaxid set dates=getdate()
--取出id
select @id=id from studentmaxid
if @id is null
begin
rollback tran
raiserror('System data is missing',16,1)
return
end
declare cr_student_insert cursor local for select name From Inserted
open cr_student_insert
fetch next from cr_student_insert into @name
set
while @@fetch_status=0
begin
set @id=@id+1
update student set id=@id where name=@name
fetch next from cr_student_insert into @name
end
close cr_student_insert
deallocate cr_student_insert
update studentmaxid set id=@id,dates=getdate()
commit tran
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER dbo.tr_student_delete ON [dbo].[student]
FOR DELETE
AS
set nocount on
declare @cnt int
select @cnt=count(*) from deleted
if @cnt=0
return
begin tran
declare @id int
--锁定 studentmaxid
update studentmaxid set dates=getdate()
select @id=id from studentmaxid
if @id is null
begin
rollback tran
raiserror('System data is missing',16,1)
return
end
declare cr_student_deleted cursor local for select id From deleted order by 1 desc
open cr_student_deleted
fetch next from cr_student_deleted into @id
while @@fetch_status=0
begin
update student set id=id-1 where id>@id
fetch next from cr_student_deleted into @id
end
close cr_student_deleted
deallocate cr_student_deleted
update studentmaxid set id=id-@cnt,dates=getdate()
commit tran
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO