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

2004-07-29 04:19:34 215 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

硬件开发流程研发流程在一个有一定规模的企业中起到了非常重要的作用，也是一个产品或者项目成功量产出货依赖的平台体系，作为工程师如果想要走的更远，就必须了解流程体系，了解如何对产品的开发质量进行更好的管控。... 阅读详情

我想用触发器来按照不同的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

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

...全文

使用3dmax Morpher制作Unity表情动画关于游戏人物的面部表情动画方案一：如果面部骨骼数丰富的话，可以直接做骨骼动画方案二：采用变形器（3dmax是Morpher，maya是blendshape）修改相应通道的权重值来做表情动画本文简述方案二的做法。说明：本文截图中3dsMax版本为2017。笔者第一次使用3dsMax，欢迎指正。 3dmax中导入人物模型，选择需要变形的对象，即head。当前对象附带skin信息。 ... 阅读详情

Kidwind

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

Kidwind 2004-07-29

打赏
举报

游标的方法我已经试过了，但由于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

cqdj 2004-07-29

打赏
举报

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

Kidwind 2004-07-29

打赏
举报

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

Kidwind 2004-07-29

打赏
举报

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

victorycyz 2004-07-29

打赏
举报

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

Kidwind 2004-07-29

打赏
举报

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

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

相关推荐