关于触发器的问题，高分求助！！！！

[Kidwind]

我想用触发器来按照不同的ClassID值来自动递增字段ID的值，我按照以下的方法在插入一条记录时正常，但如果进行多行同时插入时，就是用insert table select * from table2这样的形式插入时，新插入的ID字段值都相同，并未像我预想中的那样自动递增。

CREATE TRIGGER [MaintenanceID_Insert] ON [dbo].[OptionItem]
INSTEAD OF INSERT
AS

insert into OptionItem (ClassID, ID, RootID, PID, Layer, [Order], Child, Name, Comment)
select ClassID, ID = (select isnull(Max(ID), 0) + 1 from OptionItem where ClassID = inserted.ClassID), RootID, PID, Layer, [Order], Child, Name, Comment from inserted


后来我改成用游标的方式，逐条的进行插入，但后来才发现在inserted表中的记录其OptionID字段全部为0，因此我无法定位到inserted中的某条记录，因此假如同时插入25条记录，真实的就会出现25*25=625条记录。

Declare @OptionID int, @ClassID int

Declare InsertCollection cursor for select OptionID, ClassID from inserted
open InsertCollection

fetch next from InsertCollection into @OptionID, @ClassID
while @@FETCH_STATUS <> -1
begin
--update OptionItem set ID = (select isnull(Max(ID), 0) + 1 from OptionItem where ClassID = @ClassID) where OptionID = @OptionID
--insert into OptionItem (ClassID, ID, RootID, PID, Layer, [Order], Child, Name, Comment)
-- select ClassID, ID = (select isnull(Max(ID), 0) + 1 from OptionItem where ClassID = @ClassID), RootID, PID, Layer, [Order], Child, Name, Comment from inserted where OptionID = @OptionID
select * from inserted where OptionID = @OptionID
fetch next from InsertCollection into @OptionID, @ClassID
end
CLOSE InsertCollection
DEALLOCATE InsertCollection


请问这样的问题我应该如何解决呢？？

[Kidwind]

没人知道吗？？？自己顶一下！！！

[victorycyz]

请给出两表的结构，你的触发器要实现的功能。最好还给一些示例数据。

[Kidwind]

OptionClass是个分类表
OptionItem是项目表，其ClassID为OptionClass表的ClassID的处键。我想用触发器来维护每个不同类别的ID字段按其类别的ID自动递增。

这个是生成表的脚本
===================================================================
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_OptionItem_OptionClass]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[OptionItem] DROP CONSTRAINT FK_OptionItem_OptionClass
GO

/****** Object: Trigger dbo.MaintenanceID_Insert Script Date: 04-07-29 16:49:01 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MaintenanceID_Insert]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[MaintenanceID_Insert]
GO

/****** Object: Trigger dbo.MaintenanceID_Update Script Date: 04-07-29 16:49:01 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MaintenanceID_Update]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[MaintenanceID_Update]
GO

/****** Object: Table [dbo].[OptionItem] Script Date: 04-07-29 16:49:01 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OptionItem]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[OptionItem]
GO

/****** Object: Table [dbo].[OptionClass] Script Date: 04-07-29 16:49:01 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OptionClass]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[OptionClass]
GO

/****** Object: Table [dbo].[OptionClass] Script Date: 04-07-29 16:49:02 ******/
CREATE TABLE [dbo].[OptionClass] (
[ClassID] [int] IDENTITY (1, 1) NOT NULL ,
[ClassName] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Comment] [ntext] COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

/****** Object: Table [dbo].[OptionItem] Script Date: 04-07-29 16:49:03 ******/
CREATE TABLE [dbo].[OptionItem] (
[OptionID] [int] IDENTITY (1, 1) NOT NULL ,
[ClassID] [int] NOT NULL ,
[ID] [int] NOT NULL ,
[RootID] [int] NOT NULL ,
[PID] [int] NOT NULL ,
[Layer] [int] NOT NULL ,
[Order] [int] NOT NULL ,
[Child] [int] NOT NULL ,
[Name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Comment] [ntext] COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[OptionClass] WITH NOCHECK ADD
CONSTRAINT [PK_OptionClass] PRIMARY KEY CLUSTERED
(
[ClassID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[OptionItem] WITH NOCHECK ADD
CONSTRAINT [PK_OptionItem] PRIMARY KEY CLUSTERED
(
[OptionID],
[ClassID],
[ID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[OptionItem] ADD
CONSTRAINT [DF_OptionItem_ID] DEFAULT (0) FOR [ID],
CONSTRAINT [DF_OptionItem_RootID] DEFAULT (0) FOR [RootID],
CONSTRAINT [DF_OptionItem_PID] DEFAULT (0) FOR [PID],
CONSTRAINT [DF_OptionItem_Layer] DEFAULT (0) FOR [Layer],
CONSTRAINT [DF_OptionItem_Order] DEFAULT (0) FOR [Order],
CONSTRAINT [DF_OptionItem_Child] DEFAULT (0) FOR [Child]
GO

ALTER TABLE [dbo].[OptionItem] ADD
CONSTRAINT [FK_OptionItem_OptionClass] FOREIGN KEY
(
[ClassID]
) REFERENCES [dbo].[OptionClass] (
[ClassID]
) ON DELETE CASCADE
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/****** Object: Trigger dbo.MaintenanceID_Insert Script Date: 04-07-29 16:49:03 ******/
CREATE TRIGGER [MaintenanceID_Insert] ON [dbo].[OptionItem]
INSTEAD OF INSERT
AS

insert into OptionItem (ClassID, ID, RootID, PID, Layer, [Order], Child, Name, Comment)
select ClassID, ID = (select isnull(Max(ID), 0) + 1 from OptionItem where ClassID = inserted.ClassID), RootID, PID, Layer, [Order], Child, Name, Comment from inserted
update OptionItem set RootID = ID where PID = 0 and RootID <> ID-- and OptionID in (select OptionID from inserted)

/*
Declare @OptionID int, @ClassID int

Declare InsertCollection cursor for select OptionID, ClassID from inserted
open InsertCollection

fetch next from InsertCollection into @OptionID, @ClassID
while @@FETCH_STATUS <> -1
begin
--update OptionItem set ID = (select isnull(Max(ID), 0) + 1 from OptionItem where ClassID = @ClassID) where OptionID = @OptionID
--insert into OptionItem (ClassID, ID, RootID, PID, Layer, [Order], Child, Name, Comment)
-- select ClassID, ID = (select isnull(Max(ID), 0) + 1 from OptionItem where ClassID = @ClassID), RootID, PID, Layer, [Order], Child, Name, Comment from inserted where OptionID = @OptionID
select * from inserted where OptionID = @OptionID
fetch next from InsertCollection into @OptionID, @ClassID
end
CLOSE InsertCollection
DEALLOCATE InsertCollection

update OptionItem set RootID = ID where PID = 0 and RootID <> ID
*/
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/****** Object: Trigger dbo.MaintenanceID_Update Script Date: 04-07-29 16:49:03 ******/
CREATE TRIGGER [MaintenanceID_Update] ON [dbo].[OptionItem]
INSTEAD OF UPDATE
AS
if not update(ID)
begin
Update a set
ClassID = i.ClassID,
RootID= i.RootID,
PID = i.PID,
Layer = i.Layer,
[Order] = i.[Order],
Child = i.Child,
Name = i.Name,
Comment = i.Comment
from OptionItem a,inserted i
where a.OptionID=i.OptionID
end

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

[Kidwind]

急啊！！！谢了各位兄弟！！！

[cqdj]

记住啊兄弟，mssql的触发器在你insert是（一个递交事务更合适）只执行一次！是表级，不是行级，所以才出现你那样的问题。建议你最好用游标。

[Kidwind]

游标的方法我已经试过了，但由于inserted表中的数据没有插入到真实的表中，所以OptionID都是为0的，所以我无法用以下的方法定位到inserted中的某行


Declare @OptionID int, @ClassID int

Declare InsertCollection cursor for select OptionID, ClassID from inserted
open InsertCollection

fetch next from InsertCollection into @OptionID, @ClassID
while @@FETCH_STATUS <> -1
begin
--update OptionItem set ID = (select isnull(Max(ID), 0) + 1 from OptionItem where ClassID = @ClassID) where OptionID = @OptionID
--insert into OptionItem (ClassID, ID, RootID, PID, Layer, [Order], Child, Name, Comment)
-- select ClassID, ID = (select isnull(Max(ID), 0) + 1 from OptionItem where ClassID = @ClassID), RootID, PID, Layer, [Order], Child, Name, Comment from inserted where OptionID = @OptionID
select * from inserted where OptionID = @OptionID
fetch next from InsertCollection into @OptionID, @ClassID
end
CLOSE InsertCollection
DEALLOCATE InsertCollection