SQL Server中的魔术表

There are Magic Tables (virtual tables) in SQL Server that hold the temporal information of recently inserted and recently deleted data in the virtual table. INSERTED and DELETED are two types of magic tables in SQL Server. An INSERTED magic table is populated with INSERT and UPDATE operations and DELETED magic table is populated with UPDATE and DELETE operations.

SQL Server中有魔术表（虚拟表），用于保存虚拟表中最近插入和最近删除的数据的时间信息。 插入和删除是SQL Server中的魔术表的两种类型。 将使用INSERT和UPDATE操作填充一个INSERTED魔术表，并使用UPDATE和DELETE操作填充DELETED魔术表。

The INSERTED magic table stores the before version of the row, and the DELETED table stores the after version of the row for any INSERT, UPDATE, or DELETE operations.

对于任何INSERT，UPDATE或DELETE操作，INSERTED魔术表存储该行的之前版本，而DELETED表存储该行的之后版本。

A magic table can be utilized in INSERT, UPDATE, and DELETE activity with the table in a trigger, which is the common understanding of people. SQL Server uses magic tables outside the TRIGGER also for a lot of other purposes too. Use of Magic tables in SQL Server with the usual update statement lessens the information dependency and makes your information consistent with your transaction.

可以在INSERT，UPDATE和DELETE活动中利用魔术表，并将该表放在触发器中，这是人们的共同理解。 SQL Server也将TRIGGER之外的魔术表用于许多其他目的。 在SQL Server中使用Magic表和通常的update语句可以减少信息依赖性，并使您的信息与事务保持一致。

INSERT: The INSERTED magic table will have the newly inserted rows on the top in the table with an insert operation. It can be used to manage an audit of the table to another history table.

INSERT ：通过插入操作，INSERTED魔术表将在表的顶部具有新插入的行。 它可用于管理对该表到另一个历史记录表的审核。

DELETE: The DELETED magic table will have the recently deleted rows on the top in the table with a delete operation. It can be used to manage a previous version of the row for the audit purpose in the history table.

DELETE ：DELETED魔术表将通过删除操作在表的顶部显示最近删除的行。 它可用于管理历史记录表中该行的先前版本，以进行审核。

UPDATE: Both INSERTED and DELETED virtual tables will be part of an update statement. Update statement returns deleted magic table with the previous version of the row and the inserted magic table with the new version of a row, which is going to be replaced or updated with the earlier values in the table. The important thing is that whenever users perform the update statement inside the trigger or outside the trigger, INSERTED and DELETED magic tables are being used.

UPDATE ：INSERTED和DELETED虚拟表都将成为更新语句的一部分。 Update语句返回具有该行的先前版本的已删除魔术表，以及具有该行的新版本的插入的魔术表，该魔术表将被表中的较早值替换或更新。 重要的是，每当用户在触发器内部或触发器外部执行更新语句时，都会使用INSERTED和DELETED魔术表。

A common use of Magic tables in SQL Server is the DML (Data Manipulation Language) trigger. SQL Server DML trigger allows using these two virtual tables INSERTED and DELETED. The ideal use of the trigger is auditing and managing a before and after version of the table row on INSERT, UPDATE, or DELETE operation within the transaction statement. Even users can write data manipulation logic as well with these magic tables inside the trigger.

在SQL Server中，Magic表的常见用法是DML（数据操作语言）触发器。 SQL Server DML触发器允许使用这两个虚拟表INSERTED和DELETED。 触发器的理想用法是在事务语句中的INSERT，UPDATE或DELETE操作上审核和管理表行的前后版本。 甚至用户也可以使用触发器内部的这些魔术表编写数据操作逻辑。

A magic table is stored in the temp DB. Therefore, whenever you use the magic tables in SQL Server with the query statement, tempdb will come in the picture.

魔术表存储在临时数据库中。 因此，每当您在SQL Server中将魔术表与查询语句一起使用时，就会出现tempdb。

Whenever the magic table is utilizing with a query statement in the transaction, tempdb will be affected by that statement. Below are the limitations of the magic table compare to the actual temp table (# table)

每当魔术表在事务中与查询语句一起使用时，tempdb都会受到该语句的影响。 以下是magic表与实际temp表（＃表）相比的局限性

• Users can not create any index or apply any constraint on the magic tables in SQL Server
  用户不能在SQL Server中的魔术表上创建任何索引或应用任何约束
• They can not be altered because the purpose of the magic table is to audit the information in the system
  不可更改它们，因为魔术表的目的是审核系统中的信息

如何在触发器中使用魔术表？ (How to use Magic Tables in the Trigger?)

Data manipulation with the magic table is much useful in the trigger for information auditing. Users can use join operations with the magic tables in SQL Server and compose a business logic as well. For example, a trigger to check if an update operation is performed on any particular column or not? If it happened, then execute the required statements to perform such tasks.

使用魔术表进行数据操作在信息审计的触发器中非常有用。 用户可以对SQL Server中的魔术表使用联接操作，也可以编写业务逻辑。 例如，用于检查是否对任何特定列执行更新操作的触发器？ 如果发生这种情况，请执行所需的语句以执行此类任务。

CREATE TRIGGER [Production].[uWorkOrder] ON [Production].[WorkOrder] 
AFTER UPDATE AS 
BEGIN
    SET NOCOUNT ON;
    --IF UPDATE([ProductID]) OR UPDATE([OrderQty])
    --BEGIN
    INSERT INTO [Production].[TransactionHistory](ProductID, ReferenceOrderID, TransactionType, TransactionDate, Quantity)
    SELECT i.ProductID,
        i.WorkOrderID,
        'W',
        GETDATE(),
        i.OrderQty
    FROM inserted AS i;
 
    INSERT INTO [Production].[TransactionHistory](ProductID, ReferenceOrderID, TransactionType, TransactionDate, Quantity)
    SELECT d.[ProductID], d.[WorkOrderID], 'W', GETDATE(), d.[OrderQty]
    FROM deleted AS d;
    --END;
END;

Here, uWorkOrder trigger exists inside the WorkOrder table in the Microsoft practice database (AdventureWorks). Users can write triggers with condition-based logic with the help of the UPDATE() function. UPDATE() function refers to that update operation is performed or not on the particular column, which is being used in the UPDATE() function inside the trigger. Inserted and deleted are the magic tables in the SQL Server trigger that used to manage pre-updated and post updated row.

在这里， uWorkOrder触发器存在于Microsoft练习数据库（ AdventureWorks ）的WorkOrder表中。 用户可以借助UPDATE（）函数使用基于条件的逻辑来编写触发器。 UPDATE（）函数是指在触发器内部的UPDATE（）函数中使用的特定列上是否执行了更新操作。 插入和删除的是SQL Server触发器中的魔术表，用于管理预更新和后更新的行。

The virtual table (Magic Table) will be removed from the tempdb once a transaction gets finished. In the above sample code, we are inserting both row versions (before and current) into another audit table.

事务完成后，将从临时数据库中删除虚拟表（Magic Table）。 在上面的示例代码中，我们将两个行版本（之前和当前）都插入到另一个审计表中。

更新表，使用SQL Server中的魔术表返回更新的行 (Update Table with returning an updated row using Magic Tables in SQL Server)

A magic table can be accessed outside the DML trigger as well. Most of the developers don’t know about this utilization of a magic table with an update statement. For example, when a user wants to return updated rows with the update statement. A developer will first update a row and return those rows with applying a filter, whatever filters are applied earlier with the update statement on the table. However, it could return dirty rows, because that rows can be updated by another transaction as well meanwhile.

魔术表也可以在DML触发器之外访问。 大多数开发人员都不知道如何使用带有更新语句的魔术表。 例如，当用户想要使用update语句返回更新的行时。 开发人员将首先更新一行并通过应用过滤器返回这些行，无论之前在表上的update语句中应用了什么过滤器。 但是，它可能返回脏行，因为该行同时也可以由另一个事务更新。

In the second case, users fetch and store that updatable rows into the temp table by applying a required filter on a table, perform an update operation and return those rows from the temp table in the same transaction. But inserting those rows into the temp table and before completing the update statement, meanwhile another transaction can carry those rows for the same activity which are going to be updated. To overcome this problem statement and challenges, the user can utilize the magic tables in SQL Server with the update statement, which will return deleted (early version) and inserted (new version) rows with INSERTED and DELETED.

在第二种情况下，用户通过在表上应用所需的过滤器来获取可更新行并将其存储到临时表中，执行更新操作并在同一事务中从临时表返回这些行。 但是，将这些行插入到临时表中并在完成更新语句之前，与此同时，另一个事务可以携带将要更新的相同活动的那些行。 为了克服该问题说明和挑战，用户可以在SQL Server中使用带有update语句的魔术表，该魔术表将返回具有INSERTED和DELETED的已删除（早期版本）和已插入（新版本）行。

For example, you can see the below sample table, [WorkOrder], with a filter applied to it.

例如，您可以看到下面的示例表[WorkOrder]，其中已应用了过滤器。

Now, updating a table row and fetching those rows with the use of a magic table and (#) temp table.

现在，更新表行并使用魔术表和（＃）临时表获取这些行。

CREATE TABLE #order(WorkOrderID INT, ProductID INT, OrderQty INT, StockedQty INT)
 
UPDATE [WorkOrder] WITH (ROWLOCK)
SET OrderQty = OrderQty - 1
OUTPUT INSERTED.WorkOrderID, INSERTED.ProductID, INSERTED.OrderQty, INSERTED.StockedQty
INTO #order 
WHERE ProductID = 722 AND WorkOrderID = 45
 
SELECT * FROM #order

In the above T-SQL query sample, an INSERTED magic table will return the newly inserted value and carrying it to the #order temp table. However, we are not updating the column WorkOrderID, ProductID, and StockedQty in the above query statement, but the inserted virtual table can return other columns of the table as well.

在上面的T-SQL查询示例中，INSERTED魔术表将返回新插入的值并将其携带到#order temp表中。 但是，我们没有在上面的查询语句中更新列WorkOrderID，ProductID和StockedQty，但是插入的虚拟表也可以返回表的其他列。

Here, an INSERTED magic table is used to return an updated row. The newly updated value of the column can return in the response with the help of the OUTPUT parameter in the same statement. Even users can return early versions of updated rows as well with the help of the DELETED magic table in the same T-SQL statement.

在这里，INSERTED魔术表用于返回更新的行。 在同一条语句中，借助于OUTPUT参数，可以在响应中返回该列的新更新值。 甚至用户也可以在同一T-SQL语句中借助DELETED magic表返回更新的行的早期版本。

For example,

例如，

CREATE TABLE #order(WorkOrderID INT, ProductID INT, OrderQty INT, perv_OrderQty INT)
 
UPDATE [WorkOrder] WITH (ROWLOCK)
SET OrderQty = OrderQty - 1
OUTPUT INSERTED.WorkOrderID, INSERTED.ProductID, INSERTED.OrderQty, DELETED.OrderQty as prev_OrderQty
INTO #order 
WHERE ProductID = 722 AND WorkOrderID = 45
 
SELECT * FROM #order

Here, a newly updated value is 29, which is inserted into the temp table using an INSERTED magic table and column alias with prev_OrderQty that returns the before update version of a row with the help of a DELETED magic table.

此处，新更新的值为29，该值使用INSERTED魔术表和具有prev_OrderQty的列别名插入到临时表中，prev_OrderQty借助DELETED魔术表返回行的更新前版本。

The most significant scenario is that when a user is updating (n) rows using the TOP (n) keyword with the UPDATE statement, and a user wants those rows in query results, which are updated with the UPDATE statement. We don’t have any appropriate choice to achieve it except the MAGIC tables in SQL Server. If you are using READ UNCOMMITTED ISOLATION level, then both scenarios will return a dirty read. If you are fetching rows with the READ COMMITTED ISOLATION level, then there are chances of blocking to occur with the transaction.

最重要的情况是，当用户使用带有UPDATE语句的TOP（n）关键字更新（n）行时，并且用户希望查询结果中的那些行已被UPDATE语句更新。 除了SQL Server中的MAGIC表，我们没有其他合适的选择来实现它。 如果使用的是READ UNCOMMITTED ISOLATION级别，则两种情况都将返回脏读。 如果您要以READ COMMITTED ISOLATION级别来获取行，则该事务有可能发生阻塞。

For example,

例如，

UPDATE TOP (n) TableName WITH(ROWLOCK)
SET ColumnD = ?
OUTPUT INSERTED.ColumnA, INSERTED.ColumnB, .....
INTO #OutTable
WHERE ColumnA = ?

Here, TOP (n) rows will get updated in the above UPDATE statement, and those updated rows will be inserted into the #OutTable temp table. Here, a magic table with an update statement resolves a lot of data dependencies. TOP (n) with an update statement should be useful when a multi-threading application picks data update activity on the table parallelly. Any of the free workers will pull the (n) records from a table with an update and process those rows at the application side. Even Magic tables help to decrease the code testing time as well in the above-explained scenarios.

在这里，TOP（n）行将在上面的UPDATE语句中得到更新，并且那些更新的行将被插入#OutTable临时表中。 在这里，带有更新语句的魔术表可以解决许多数据依赖性。 当多线程应用程序并行选择表上的数据更新活动时，带有update语句的TOP（n）应该很有用。 任何自由工作者都会通过更新从表中提取（n）条记录，并在应用程序端处理这些行。 在上述情况下，即使Magic表也有助于减少代码测试时间。

结论 (Conclusion)

The purpose of this article is not only to introduce the magic tables (INSERTED and DELETED) in a trigger, but users can utilize it outside the trigger with a regular update statement as well.

本文的目的不仅是在触发器中引入魔术表（INSERTED和DELETED），而且用户还可以通过常规更新语句在触发器之外使用它。

翻译自: https://www.sqlshack.com/magic-tables-in-sql-server/