CODEWITHSUNDEEP
sqlsql-servertriggers
Cole MacDonald
Cole MacDonald

Reputation: 33

SQL Trigger to add data from other table if there is an entry match

I've got two tables one: 

ID | TimeStamp | Data
---|-----------|------ 
1  | 10/10/2015| 54
2  | 11/10/2015| 493
3  | 12/10/2015| 293

and the other: 

ID | Interval | Location
---|----------|------ 
1  | 23       | New York
2  | 22       | London
3  | 21       | Paris

I would like to have an SQL trigger that executes when a new entry is added to the first table. The trigger would then combine the data from both tables into another table... something like this: 

ID | TimeStamp | Data | Interval | Location
---|-----------|------|----------|------ 
1  | 10/10/2015|    54| 23       | New York
2  | 11/10/2015|   493| 22       | London
3  | 12/10/2015|   293| 21       | Paris

I'm new to SQL, and have looked into both triggers and join requests. If someone could point me into the right direction to write a query it would be amazing.

Upvotes: 2

Views: 136

Answers (1)

Nir Levy
Nir Levy

Reputation: 12953

I think the correct way to go in this case would be to use join (either as a query or create a view), it sounds like using a trigger and keeping the data twice is redundant.

The join here is quite simple, just inner join on the ID field:

SELECT f.ID, f.timestamp, f.data, second.location, second.interval from
  firstTable as f inner join secondTable as s on f.ID = s.ID;

If you want it as a view, simply do:

CREATE VIEW joinView AS
SELECT f.ID, f.timestamp, f.data, second.location, second.interval from
  firstTable as f inner join secondTable as s on f.ID = s.ID;

Upvotes: 1

Related Questions