Inserted updated and deleted keys in SQL Server language

Inserted, updated, and deleted keys in SQL Server play a crucial role when dealing with data manipulation operations like INSERT, UPDATE, and DELETE within SQL Server triggers. These special tables hold the data affected by these operations and can be used to access and analyze the changes made in the database.

Here's how these keys are typically implemented in SQL Server programming language:

1. Inserted Table: The `INSERTED` table holds copies of the affected rows that were inserted during an `INSERT` operation. It allows you to access the newly added data within a trigger or query after the `INSERT` operation has been executed.


   CREATE TRIGGER trgAfterInsert
   ON tableName
   AFTER INSERT
   AS
   BEGIN
       SELECT * FROM INSERTED;
   END;

2. Deleted Table: The `DELETED` table stores copies of the affected rows that were deleted during a `DELETE` operation. It helps you to access the removed data within a trigger or query after the `DELETE` operation has been performed.


   CREATE TRIGGER trgAfterDelete
   ON tableName
   AFTER DELETE
   AS
   BEGIN
       SELECT * FROM DELETED;
   END;

3. Updated Table: The `INSERTED` and `DELETED` tables work together when handling `UPDATE` operations. The `INSERTED` table contains the new values after the `UPDATE`, while the `DELETED` table stores the original values before the update.


   CREATE TRIGGER trgAfterUpdate
   ON tableName
   AFTER UPDATE
   AS
   BEGIN
       SELECT * FROM INSERTED; -- New values
       SELECT * FROM DELETED; -- Old values
   END;

These special tables are essential for trigger logic and auditing purposes, allowing you to track and manage changes to data in SQL Server effectively. By leveraging the `INSERTED`, `UPDATED`, and `DELETED` keys, developers can create robust and dynamic database processes that respond to data modifications in a precise and controlled manner.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Mohanty1y ago
In SQL Server, the INSERTED and DELETED tables are special logical tables used primarily within triggers. They are not real tables you define or create, they are virtual and used to track data changes during DML operations like INSERT, UPDATE, and DELETE.

INSERTED and DELETED are system-generated logical tables automatically available inside DML triggers. You do not create these tables manually. They are temporary and exist only during the trigger execution.
0
Emily Foster1y ago
Inserted, updated, and deleted keys in SQL Server play a crucial role when dealing with data manipulation operations like INSERT, UPDATE, and DELETE within SQL Server triggers. These special tables hold the data affected by these operations and can be used to access and analyze the changes made in the database.

Here's how these keys are typically implemented in SQL Server programming language:

1. Inserted Table: The `INSERTED` table holds copies of the affected rows that were inserted during an `INSERT` operation. It allows you to access the newly added data within a trigger or query after the `INSERT` operation has been executed.
```
   CREATE TRIGGER trgAfterInsert
   ON tableName
   AFTER INSERT
   AS
   BEGIN
       SELECT * FROM INSERTED;
   END;
```
2. Deleted Table: The `DELETED` table stores copies of the affected rows that were deleted during a `DELETE` operation. It helps you to access the removed data within a trigger or query after the `DELETE` operation has been performed.
```
   CREATE TRIGGER trgAfterDelete
   ON tableName
   AFTER DELETE
   AS
   BEGIN
       SELECT * FROM DELETED;
   END;
```
3. Updated Table: The `INSERTED` and `DELETED` tables work together when handling `UPDATE` operations. The `INSERTED` table contains the new values after the `UPDATE`, while the `DELETED` table stores the original values before the update.
```
   CREATE TRIGGER trgAfterUpdate
   ON tableName
   AFTER UPDATE
   AS
   BEGIN
       SELECT * FROM INSERTED; -- New values
       SELECT * FROM DELETED; -- Old values
   END;
```
These special tables are essential for trigger logic and auditing purposes, allowing you to track and manage changes to data in SQL Server effectively. By leveraging the `INSERTED`, `UPDATED`, and `DELETED` keys, developers can create robust and dynamic database processes that respond to data modifications in a precise and controlled manner.
0