How Can SQL Server Triggers Insert Data from New Rows into Another Table?

Inserting Values from New Rows into Another Table Using SQL Server Triggers

Implementing triggers in SQL Server offers a convenient way to automate data operations based on certain events. As described in the question, a trigger can be set up on the aspnet_users table to capture new insertions and populate another table with relevant information.

To retrieve the values from the newly inserted row, SQL Server provides the inserted pseudo-table, which contains the columns and data of the inserted row. This eliminates the need to rely on potentially unreliable methods like comparing timestamps.

The following SQL statement demonstrates how to create a trigger that inserts the user_id and user_name values from the new row into a separate table:

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

In this example:

• yourNewTrigger is the name you assign to the trigger.
• yourSourcetable is the name of the table where the trigger will be executed on insertion.
• yourDestinationTable is the name of the table where you want to insert the values.

The code uses the inserted pseudo-table to access the user_id and user_name columns from the new row. It also specifies additional column values ('a', 'default', and null) as required by your destination table structure.

Once defined, this trigger will automatically execute whenever a new record is added to the yourSourcetable table, ensuring that the corresponding data is added to the yourDestinationTable table with minimal manual intervention.

The above is the detailed content of How Can SQL Server Triggers Insert Data from New Rows into Another Table?. For more information, please follow other related articles on the PHP Chinese website!