共有三个表,下面是创建表的脚本:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_订单状态_订单]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[订单状态] DROP CONSTRAINT FK_订单状态_订单
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[订单_insert]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[订单_insert]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[报警项目]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[报警项目]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[订单]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[订单]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[订单状态]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[订单状态]
GOCREATE TABLE [dbo].[报警项目] (
[报警代码] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[内容] [nvarchar] (1000) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[订单] (
[订单号] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[订单状态] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[订单号] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[订单状态代码] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GOALTER TABLE [dbo].[报警项目] ADD
CONSTRAINT [PK_OrderAlert] PRIMARY KEY CLUSTERED
(
[报警代码]
) ON [PRIMARY]
GOALTER TABLE [dbo].[订单] ADD
CONSTRAINT [PK_tblOrder] PRIMARY KEY CLUSTERED
(
[订单号]
) ON [PRIMARY]
GOALTER TABLE [dbo].[订单状态] ADD
CONSTRAINT [PK_OrderStatus] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GOALTER TABLE [dbo].[订单状态] ADD
CONSTRAINT [FK_订单状态_订单] FOREIGN KEY
(
[订单号]
) REFERENCES [dbo].[订单] (
[订单号]
) ON DELETE CASCADE ON UPDATE CASCADE
GOSET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOCREATE TRIGGER 订单_insert
ON 订单
FOR insert
AS
BEGIN
declare @bh as varchar(20)
begin
select @bh= 订单号 from inserted
INSERT INTO 订单状态 (订单号, 订单状态代码) SELECT @bh, 报警代码 FROM 报警项目
end
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO工作流程如下:订单表有一个触发器,在添加新数据时,取出报警项目表内的数据,然后加上订单号插入到订单状态表内,现在的问题是:
报警项目表内有一行数据时,在订单表内添加数据不会出错,如果报警项目表内有两行数据时,在订单表内一添加数据就提示:“键列信息不足或不正确,更新影响到多行?”为什么呢?
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_订单状态_订单]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[订单状态] DROP CONSTRAINT FK_订单状态_订单
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[订单_insert]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[订单_insert]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[报警项目]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[报警项目]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[订单]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[订单]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[订单状态]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[订单状态]
GOCREATE TABLE [dbo].[报警项目] (
[报警代码] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[内容] [nvarchar] (1000) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[订单] (
[订单号] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[订单状态] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[订单号] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[订单状态代码] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GOALTER TABLE [dbo].[报警项目] ADD
CONSTRAINT [PK_OrderAlert] PRIMARY KEY CLUSTERED
(
[报警代码]
) ON [PRIMARY]
GOALTER TABLE [dbo].[订单] ADD
CONSTRAINT [PK_tblOrder] PRIMARY KEY CLUSTERED
(
[订单号]
) ON [PRIMARY]
GOALTER TABLE [dbo].[订单状态] ADD
CONSTRAINT [PK_OrderStatus] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GOALTER TABLE [dbo].[订单状态] ADD
CONSTRAINT [FK_订单状态_订单] FOREIGN KEY
(
[订单号]
) REFERENCES [dbo].[订单] (
[订单号]
) ON DELETE CASCADE ON UPDATE CASCADE
GOSET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOCREATE TRIGGER 订单_insert
ON 订单
FOR insert
AS
BEGIN
declare @bh as varchar(20)
begin
select @bh= 订单号 from inserted
INSERT INTO 订单状态 (订单号, 订单状态代码) SELECT @bh, 报警代码 FROM 报警项目
end
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO工作流程如下:订单表有一个触发器,在添加新数据时,取出报警项目表内的数据,然后加上订单号插入到订单状态表内,现在的问题是:
报警项目表内有一行数据时,在订单表内添加数据不会出错,如果报警项目表内有两行数据时,在订单表内一添加数据就提示:“键列信息不足或不正确,更新影响到多行?”为什么呢?
我是用access连接sql server然后在access里操作的,在access里操作的时候就出这个提示,为什么呢?
另外,需要弄明白:
“键列信息不足或不正确,更新影响到多行?”
这个错误信息是SQLSERVER的(Microsoft Cursor Service)还是Access的.