How to Retrieve Inserted Row Values in a SQL Server Trigger for Cross-Table Data Insertion?

Retrieving Inserted Row Values in SQL Server Trigger for Cross-Table Insertion

To insert the user_id and user_name from a new row in the aspnet_users table into another table, a trigger can be employed.

Obtaining Last Inserted Row Values

Traditionally, retrieving the last inserted row values involved selecting rows based on the latest date_created, which can be unreliable. Instead, SQL Server provides the "inserted" pseudo-table that contains the row data that triggered the insertion.

Trigger Implementation

The following trigger, "yourNewTrigger," can be created on the "yourSourceTable" to capture insert events.

CREATE TRIGGER yourNewTrigger ON yourSourceTable
FOR INSERT
AS

INSERT INTO yourDestinationTable
        (col1, col2    , col3, user_id, user_name)
    SELECT
        'a'  , default , null, user_id, user_name
        FROM inserted

go

Explanation

• The "inserted" pseudo-table provides access to the inserted row data.
• Columns "col1" and "col2" are populated with fixed values.
• "col3" remains null if not specified in the source table.
• "user_id" and "user_name" are extracted from the inserted row.
• The inserted values are inserted into the "yourDestinationTable."

By utilizing the "inserted" pseudo-table, the trigger efficiently retrieves the last inserted row values, enabling the cross-table insertion of user-specific information.

The above is the detailed content of How to Retrieve Inserted Row Values in a SQL Server Trigger for Cross-Table Data Insertion?. For more information, please follow other related articles on the PHP Chinese website!