在MSSQL Sever 2000時有Table1的觸發器裡修改另一個表Table2
在一個窗體的ADOQuery1關聯Table1,在編輯其主鍵值時出現錯誤,
:“列的信息不足,影響到多行?”
是否ADO不能對帶有觸發器的表進行編輯操作?if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table1_update]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[Table1_update]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table1]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table2]
GOCREATE TABLE [dbo].[Table1] (
[code] [int] NOT NULL ,
[name] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[Table2] (
[code] [int] NOT NULL ,
[line] [int] NOT NULL ,
[qty] [float] NULL
) ON [PRIMARY]
GOALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[code]
) ON [PRIMARY]
GOALTER TABLE [dbo].[Table2] ADD
CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
(
[code],
[line]
) ON [PRIMARY]
GOSET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOCREATE TRIGGER [Table1_update] ON dbo.Table1
FOR UPDATE
AS
update a set a.code = b.code
from Table2 a, inserted b , deleted c
where a.code = c.codeGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
在一個窗體的ADOQuery1關聯Table1,在編輯其主鍵值時出現錯誤,
:“列的信息不足,影響到多行?”
是否ADO不能對帶有觸發器的表進行編輯操作?if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table1_update]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[Table1_update]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table1]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table2]
GOCREATE TABLE [dbo].[Table1] (
[code] [int] NOT NULL ,
[name] [varchar] (50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[Table2] (
[code] [int] NOT NULL ,
[line] [int] NOT NULL ,
[qty] [float] NULL
) ON [PRIMARY]
GOALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[code]
) ON [PRIMARY]
GOALTER TABLE [dbo].[Table2] ADD
CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
(
[code],
[line]
) ON [PRIMARY]
GOSET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOCREATE TRIGGER [Table1_update] ON dbo.Table1
FOR UPDATE
AS
update a set a.code = b.code
from Table2 a, inserted b , deleted c
where a.code = c.codeGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
但是Table2的多行記錄與Table1的一條記錄相配時就會報錯。
怎麼對編輯的表裡使用觸發器呢?
不知為何不能用clUseClient來編輯含有觸發器的表,這可能是內部的原因