SCOPE_IDENTITY和插入触发器的替代解决方案

Question

好的，我有一张没有自然键，只有一个整数标识列作为主键的表格。我想插入和检索标识值，但也想使用触发器确保某些字段始终设置。最初的设计是使用代替插入触发器，但这会破坏scope_identity。插入语句上的输出子句也被代替插入触发器破坏。所以，我想出了一个替代计划，并想知道我打算做的事情是否有明显的问题：

开始编造的例子：

    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

结束人为制造的例子。

不，我不是为了一个小日期字段而这样做 - 这只是一个例子。

我想要确保设置的字段已被移动到一个单独的表中（在TestDataModInfo中），触发器确保它被更新。这有效地允许我在插入后使用scope_identity()，并且似乎是安全的（如果我的触发器失败，则我的插入也失败）。这是不好的设计吗？如果是，为什么？

- Peter T. LaComb Jr.

---

谢谢Joel - 没有注意到我自己的粗心打字。 - Peter T. LaComb Jr.

我推测你在触发器中验证字段而不是约束，因为存在遗留数据？ - Maslow

@Maslow，触发器应该是最简单、最强大的方式，以确保某些字段始终或永远不会被更新（updateDate、createdate）。这些可以确保除非您也有权限禁用触发器，否则您无法打破这些规则。这只是另一层保护。 - Peter T. LaComb Jr.

Answer 1

如您所述，SCOPE_IDENTITY 是为此情况设计的。与 @@IDENTITY 不同，它不受 AFTER 触发器代码的影响。

除了使用存储过程，这种方法也可以。

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

编辑：SCOPE_IDENTITY 和 SQL Server 2005 中的并行性可能会有问题。

- gbn

---

如果使用“代替插入”触发器，那么你不能说scope_identity不受触发器代码的影响——它会返回null。 - Peter T. LaComb Jr.

@Peter，没错，他打字太快了。 - gbn

Answer 2

你尝试过使用：

SELECT scope_identity();

http://wiki.alphasoftware.com/Scope_Identity+in+SQL+Server+with+nested+and+INSTEAD+OF+triggers

- Danny Varod

Answer 3

你可以完全使用 INSTEAD OF 触发器，在触发器中捕获插入到主表后的值，然后在触发器末尾欺骗将 Scope_Identity() 放入 @@Identity 中：

-- Inside of trigger
SET NOCOUNT ON;
INSERT dbo.YourTable VALUES(blah, blah, blah);
SET @YourTableID = Scope_Identity();

-- ... other DML that inserts to another identity-bearing table

-- Last statement in trigger
SELECT YourTableID INTO #Trash FROM dbo.YourTable WHERE YourTableID = @YourTableID;

或者，这里有一个替代的最终语句，它不使用任何读取操作，但如果执行用户没有权限可能会引起问题（尽管有解决方案）。

SET @SQL =
   'SELECT identity(smallint, ' + Str(@YourTableID) + ', 1) YourTableID INTO #Trash';
EXEC (@SQL);

请注意，在某些情况下，即使使用此欺骗方法，带有INSTEAD OF触发器的表上的Scope_Identity()也可能返回NULL。但是，您可以使用@@Identity至少获取值。这可以使MS Access ADP项目在因为您在前端插入到表中而放置触发器后再次正常工作。

此外，请注意，任何并行性都可能导致@@Identity和Scope_Identity()返回不正确的值，因此请使用OPTION (MAXDOP 1)、TOP 1或单行VALUES子句来解决此问题。

- ErikE

Answer 4

你尝试过使用 OUTPUT 将值返回吗？

- HLGEM

---

当使用触发器时，使用instead of insert触发器时输出也会返回null。 - Peter T. LaComb Jr.

那么，我想你确实别无选择，只能使用一个后置触发器。 - HLGEM