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 INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
insert 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 INSERT
AS
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 int
insert into dbo.B( [JobID] )
SELECT [JobID] FROM inserted
SET @ID = SCOPE_IDENTITY()
SELECT @ID
END
修改后解决该问题