Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server trigger on insert and how to reference the data that was inserted

High level I have two tables that need to have some of the data mirrored. I can't go through and change all of the code to write to both so I thought I'd use a SQL trigger to insert data into the 2nd table anytime data is inserted into the 1st. Here is where I am stuck:

CREATE TRIGGER new_trigger_INSERT
ON old_table
FOR INSERT
INSERT INTO new_table (id, first_name, last_name)
VALUES () --This is where I'm lost, I need to insert some of the data from the insert that executed this trigger

Any help is appreciated, also if there is a better way to accomplish this let me know.

like image 222
jon3laze Avatar asked Sep 01 '25 10:09

jon3laze


2 Answers

Use the 'inserted' table:

CREATE TRIGGER new_trigger_INSERT 
ON old_table 
FOR INSERT 
INSERT INTO new_table (id, first_name, last_name) 
SELECT col1, col2, col3 FROM inserted

[PS: Don't forget to ensure your triggers handle multiple rows...]

Ref. Create Trigger

Good article: Exploring SQL Server Triggers

like image 181
Mitch Wheat Avatar answered Sep 03 '25 16:09

Mitch Wheat


In the triggers you have "inserted" and "deleted" tables. In this case you only use the "inserted" table, but in update trigger you use both.

like image 21
kubal5003 Avatar answered Sep 03 '25 16:09

kubal5003