创建触发器数据的临时表

Question

我试图创建一个审计触发器，而不必多次指定列列表。

为此，我想在触发器中生成一个临时表，其中包含INSERTED或DELETED数据的内容，然后将其处理成审计表。

如果我使用以下代码：

IF @ChangeType = 'D'
  SELECT * INTO #tmp FROM DELETED
ELSE
  SELECT * INTO #tmp FROM INSERTED

然后我在第二个SELECT * INTO出现编译错误，因为表#tmp已经存在。

如果我尝试使用动态SQL进行解决：

SET @Sql = 'SELECT * INTO #tmp FROM '
IF @ChangeType = 'D'
   SET @Sql = @Sq + 'DELETED'
ELSE
   SET @Sql = @Sql + 'INSERTED'

EXEC (@Sql)

然后我收到了一个错误，指出DELETED和INSERTED表不存在。

我应该如何将触发器中的INSERTED和DELETED表放入临时表或其他内存表中？

- Craig

---

为什么你不想多次指定列列表呢？我相信你在这个问题上花费的时间比编写SQL还要长。 :) - Tony

唯一的原因是因为不应该有必要多次指定它们。我想“在进行更改时消除错误的可能性”是最好的解释。 - Craig

Answer 1

尝试在if之外创建临时表，例如：

SELECT TOP 0 * INTO #tmp FROM DELETED

IF @ChangeType = 'D'
  INSERT INTO #tmp SELECT * FROM DELETED
ELSE
  INSERT INTO #tmp SELECT * FROM INSERTED

- Andomar

---

我一直使用"WHERE 1=0"，但读了这篇文章后感到惭愧。"TOP 0"更明确地说明了正在进行的操作，所以我投票支持我应该做的事情，而不是我一直在做的事情。 - MatBailie

Answer 2

这是一个已知的问题，由于临时表对象的解析后解析导致。在同一作用域中有两个SELECT - INTO语句，SQL Server就会放弃。

SELECT * INTO #tmp FROM DELETED WHERE 1=0
IF @ChangeType = 'D'
  INSERT #tmp SELECT * FROM DELETED
ELSE
  INSERT #tmp SELECT * FROM INSERTED

- RichardTheKiwi

Answer 3

我很想知道你为什么需要首先将数据复制到另一个表中。但这是离题的...

临时表(#temp)被认为是存储在磁盘上的，而表变量(@temp)被认为只存在于内存中，对于小任务可能更优化。(假设对表的写入通常只影响少量行)

然而，可以使用SELECT INTO技巧创建临时表，避免事先了解表定义的需要。

然而，如果您事先知道表的定义，难道不能简单地使用以下内容吗？

DECLARE @temp TABLE (id AS INT, val as INT)

IF @ChangeType = 'D'
   INSERT INTO @temp SELECT * FROM DELETED
ELSE
   INSERT INTO @temp SELECT * FROM INSERTED

个人而言，如果可能的话，我甚至会避免使用*。 因为您随后的查询仅使用特定字段，所以我只会复制我正在使用的字段。这样做的另一个好处是，如果向表中添加字段，代码不会出现错误。

DECLARE @temp TABLE (id AS INT, val as INT)

IF @ChangeType = 'D'
   INSERT INTO @temp SELECT id, val FROM DELETED
ELSE
   INSERT INTO @temp SELECT id, val FROM INSERTED

在我看来，指定字段（这是你想要避免的）的优点是，你可以确保始终只复制所需内容。

- MatBailie

---

所有字段最终都会被使用，因此我选择了所有字段。这里的重点是消除多次指定字段列表的需要，从而减少在进行更改时出现错误的可能性。最终，不得不多次指定相同的列列表似乎非常愚蠢。 - Craig

表变量和#临时表的区别在于日志记录、重新编译和统计信息，而不是内存与磁盘。尝试使用declare @a table(c int) insert into @a select 1 union all select 2 select sys.fn_PhysLocFormatter(%%physloc%%) from @a，您将看到行在tempdb中的文件:页:插槽。当然，该页面可能永远不会刷新到磁盘，但对于#temp表也是如此。 - Martin Smith