Linq to Sql 中使用Instead of insert触发器报错处理

来源：互联网发布：安卓版手机双色球软件编辑：程序博客网时间：2024/06/03 14:45

SQl server中创建一个表，需要在插入表A前做些判断，符合某些条件的插入另外一个表B中，其他插入表A

为了满足该项功能，采用Instead of Insert 触发器，表A中有自增变量，表B中也存在自增变量

CREATE TABLE [dbo].[A](  [ID] [int] IDENTITY(1,1) NOT NULL,[JobID] [int] NOT NULL  CONSTRAINT [PK_A_1] PRIMARY KEY CLUSTERED   (   [JobID] ASC   )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]  ) ON [PRIMARY]

CREATE TABLE [dbo].[B](  [ID] [int] IDENTITY(1,1) NOT NULL,[JobID] [int] NOT NULL  CONSTRAINT [PK_B_1] PRIMARY KEY CLUSTERED   (   [JobID] ASC   )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]  ) ON [PRIMARY]

表A创建insert触发器

CREATE TRIGGER triA    ON  dbo.A    INSTEAD OF INSERTAS BEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;    -- Insert statements for trigger hereinsert into dbo.B( [JobID] )SELECT [JobID] FROM inserted           END

测试时使用的是Linq to SQL 调用，插入表A数据会导致下面的错误。
Member AutoSync failure. For members to be AutoSynced after insert, the type must either have an auto-generated identity, or a key that is not modified by the database after insert.
原因是在插入带自增变量的时候，Linq必须要刚刚插入后的主键值，若INSTEAD OF它时就不能返回这个值了
修改insert触发器之后

CREATE TRIGGER triA    ON  dbo.A    INSTEAD OF INSERTAS BEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;    -- Insert statements for trigger here        DECLARE @ID intinsert into dbo.B( [JobID] )SELECT [JobID] FROM inserted                SET @ID = SCOPE_IDENTITY()       SELECT @ID           END

修改后解决该问题

0 0