How to Use a Trigger to Insert a Primary Key into Another Table in SQL

Learn how to automatically insert a primary key from one table into another using SQL triggers, ensuring efficient database management.
---
This video is based on the question https://stackoverflow.com/q/68920080/ asked by the user 'Maik Hasler' ( https://stackoverflow.com/u/16047487/ ) and on the answer https://stackoverflow.com/a/68920253/ provided by the user 'Thom A' ( https://stackoverflow.com/u/2029983/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to insert a primary key into another table with a trigger after inserting it in the first table?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/licensing
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Use a Trigger to Insert a Primary Key into Another Table in SQL

When managing databases, it's common to perform operations across multiple tables. One typical scenario is inserting data into one table and wanting to automatically propagate specific information, such as a primary key, into another table. In this guide, we will explore how to achieve this using SQL triggers.

The Problem

Imagine you have two tables in your database:

tbl_Person, which stores information about individuals, including their unique identifier (ID).

tbl_Address, which relates to the ID from the tbl_Person table but holds additional information like their addresses.

Table Structure

Here's an overview of the table structures:

tbl_Person

[[See Video to Reveal this Text or Code Snippet]]

tbl_Address

[[See Video to Reveal this Text or Code Snippet]]

You want a mechanism to automatically insert a new record into tbl_Address with the ID of a newly inserted person in tbl_Person, while setting other attributes in tbl_Address to NULL.

The Solution: Using an SQL Trigger

To accomplish this task, you can define a SQL trigger that responds to new inserts in the tbl_Person table. This trigger will take the newly inserted ID and insert it into the tbl_Address table. Let's break down the steps for creating this trigger:

Step 1: Define the Trigger

You will need to create a trigger that uses the inserted pseudo-table to access newly added rows. The following SQL code demonstrates how to set up this trigger:

[[See Video to Reveal this Text or Code Snippet]]

Understanding the Code

Trigger Name - trg_CreatePersonAddress: This is the name we are giving to our trigger.

Event - AFTER INSERT: This specifies that the trigger will execute after a new record is inserted into tbl_Person.

INSERT Command: Inside the trigger, we use the INSERT INTO statement to insert data into tbl_Address.

Selecting Data from Inserted Rows: We use INSERTED, which is a special table that holds a copy of the affected rows during the insert operation. The SELECT ID FROM inserted; line retrieves the IDs of newly added records to insert them into tbl_Address.

Conclusion

By creating a trigger as outlined above, you can automate the process of maintaining data consistency between related tables in your SQL database. This approach not only saves time but also reduces the likelihood of errors associated with manual data entry. Whenever a new entry is added to tbl_Person, an appropriate entry is made in tbl_Address without additional intervention.

Harnessing the power of triggers in SQL can significantly improve your database management practices!

Видео How to Use a Trigger to Insert a Primary Key into Another Table in SQL канала vlogize