How Can I Efficiently Trigger Data Injection into a Second Table Upon INSERT in SQL Server?

Triggering Data Injection into Another Table upon Insert in SQL Server

When managing data in SQL Server, triggers can prove immensely useful in automating operations and ensuring data integrity. One such requirement is the insertion of data from a newly created row into a separate table. Consider the following scenario:

You have a membership schema implemented in ASP.NET and wish to track user details, specifically their user ID and username, in another table upon insertion into the aspnet_users table. The question arises: how can you retrieve the values from the most recent insert efficiently?

While selecting by the most recent date created seems like a straightforward approach, it can be a less than elegant solution. Thankfully, there exists a more suitable method:

Using INSERTED Table Variable

SQL Server provides an INSERTED table variable that contains information about the newly inserted rows. This variable can be leveraged to populate the target table effortlessly:

CREATE TRIGGER [yourNewTrigger] ON [yourSourcetable]
FOR INSERT
AS
BEGIN

    INSERT INTO [yourDestinationTable]
        (col1, col2, col3, user_id, user_name)
    SELECT
        'a', default, NULL, user_id, user_name
        FROM INSERTED;

END;

In this trigger, upon insertion into yourSourcetable, the INSERTED table variable is used to extract the required data (user_id and user_name) and insert it into yourDestinationTable along with any other desired data.

This approach offers a clean and effective way to track data changes and maintain data synchronization between multiple tables, ensuring that you have the necessary information when you need it.

The above is the detailed content of How Can I Efficiently Trigger Data Injection into a Second Table Upon INSERT in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!