Inserting Records into Two Tables at Once


How can I use one INSERT statement and an INSERT trigger to add records to two tables at the same time?

To add records to two tables at the same time, you need to direct your INSERT statement at the first table. Then, on that table, create an INSERT trigger that uses the inserted table to add records to the second table. Listing 3 shows sample code that performs these operations.

The most important tip to remember about writing this statement is that inserting records into one table, activating the INSERT trigger, and adding records to a second table take place in the same transaction and at the same isolation level. Therefore, you need to avoid creating a long-running trigger that delays your users. Note that this answer assumes that the two tables sustain a data relationship. Otherwise, wrapping two separate INSERT statements in a transaction would be more efficient.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.