SCOPE_IDENTITY，而不是Insert Trigger解决方法 [英] SCOPE_IDENTITY And Instead of Insert Trigger work-around

问题描述

好的，我有一个没有自然键的表，只有一个整数标识列作为它的主键。我想插入和检索身份值，但也要使用触发器来确保始终设置某些字段。最初，设计是使用而不是插入触发器，但是这样会破坏scope_identity。插入语句上的输出子句也被插入触发器代替了。因此，我想出了一个替代方案，想知道我打算做的事情是否有明显的错误：

开始人为的例子：

 创建表[dbo]。[TestData]（
 [TestId] [int] IDENTITY（1,1）PRIMARY KEY NOT NULL，
 [Name] [nchar]（10）NOT NULL）
 
 CREATE TABLE [dbo]。[TestDataModInfo]（
 [TestId] [int]主键不是NULL，
 [RowCreateDate] [datetime]不为NULL） 
参考[dbo]。[TestData]（[TestId]）在删除级联上
 
创建触发器[dbo]。[TestData $ AfterInsert] 
在[dbo]。[TestData ] 
在插入
之后，作为
开始
-添加SET NOCOUNT ON以防止
产生额外的结果集-干扰SELECT语句。 
开启NOCOUNT； 
插入[dbo]。[TestDataModInfo] 
（[TestId]，
 [RowCreateDate]）
选择
 [TestId]，
 current_timestamp 
 FROM插入的
 
-在此处插入要触发的语句
 
 END 
  

人为设计的示例。

不，我不是在一个小日期字段中执行此操作-只是一个示例。

我要确保设置的字段已移动到单独的表（在TestDataModInfo中），并且触发器确保对其进行了更新。此方法有效，它允许我在插入后使用scope_identity（），并且看起来很安全（如果我的after触发器失败，则我的插入失败）。这是一个不好的设计吗？如果是这样，为什么呢？与@@ IDENTITY不同，它不受AFTER触发代码的影响。

除了使用存储的proc之外，还可以。

我使用AFTER触发器进行审计，因为它们很方便...也就是说，在触发器中写入另一个表。

编辑： SQL Server 2005 cam中的SCOPE_IDENTITY和并行性有问题

OK, I have a table with no natural key, only an integer identity column as it's primary key. I'd like to insert and retrieve the identity value, but also use a trigger to ensure that certain fields are always set. Originally, the design was to use instead of insert triggers, but that breaks scope_identity. The output clause on the insert statement is also broken by the instead of insert trigger. So, I've come up with an alternate plan and would like to know if there is anything obviously wrong with what I intend to do:

begin contrived example:

    CREATE TABLE [dbo].[TestData] (
    [TestId] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
    [Name] [nchar](10) NOT NULL)

    CREATE TABLE [dbo].[TestDataModInfo](
    [TestId] [int] PRIMARY KEY NOT NULL,
    [RowCreateDate] [datetime] NOT NULL)

    ALTER TABLE [dbo].[TestDataModInfo]  WITH CHECK ADD  CONSTRAINT
    [FK_TestDataModInfo_TestData] FOREIGN KEY([TestId])
    REFERENCES [dbo].[TestData] ([TestId]) ON DELETE CASCADE

CREATE TRIGGER [dbo].[TestData$AfterInsert]
   ON [dbo].[TestData]
   AFTER INSERT
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    INSERT INTO [dbo].[TestDataModInfo]
           ([TestId],
            [RowCreateDate])
        SELECT
            [TestId],
            current_timestamp
        FROM inserted

    -- Insert statements for trigger here

END

End contrived example.

No, I'm not doing this for one little date field - it's just an example.

The fields that I want to ensure are set have been moved to a separate table (in TestDataModInfo) and the trigger ensures that it's updated. This works, it allows me to use scope_identity() after inserts, and appears to be safe (if my after trigger fails, my insert fails). Is this bad design, and if so, why?

解决方案

As you mentioned, SCOPE_IDENTITY is designed for this situation. It's not affected by AFTER trigger code, unlike @@IDENTITY.

Apart from using stored procs, this is OK.

I use AFTER triggers for auditing because they are convenient... that is, write to another table in my trigger.

Edit: SCOPE_IDENTITY and parallelism in SQL Server 2005 cam have a problem

这篇关于SCOPE_IDENTITY，而不是Insert Trigger解决方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！