Creating SQL Server triggers to copy a record from one table and populate another

Question

I want to create a trigger that will copy a record from one table and populate another after an insert or update. Users will enter a date in an inspection table and I want that date inserted or updated into an asset table date column. I am a newb to SQL and T-SQL and have not created triggers before. Guidance on how to get this setup would be appreciated.

Here's a better explanation:

The table I want to create the trigger on is called [Inspections]. Users will add new records to this table and included a DateInspected value in that row. I want the trigger to take that new DateInspected value from [Inspections] and update an existing value in a table called [Hydrants].

The [Inspections] table and the [Hydrants] table have a primary key called [Id]. How do I ensure the trigger is updating the correct existing record in the [Hydrants] table?

• Kin Shah: does this answer help ?

Answer 1

OK, Here's my update based on your newly added info. The merge statement in the trigger will insure that newly added records will be inserted into hydrants. Otherwise, records that exist between inspections and hydrants will be updated in hydrants--when inspections is updated.

You can see when you run the insert and update (after the table and trigger creation below) that the row insert in the inspections table is inserted into hydrants whereas the row update to inspections is also updated in hydrants.

create table Inspections
(
 PK int identity(1,1),
 MyDate date
)
go
create table Hydrants
(
 PK int,
 MyDate date
)
Go
CREATE TRIGGER MyTrigger
 ON Inspections
 AFTER INSERT,UPDATE
AS 
BEGIN
 SET NOCOUNT ON;
 MERGE INTO Hydrants as Target
 USING inserted as Source 
 ON (TARGET.PK = SOURCE.PK) 
 WHEN MATCHED THEN 
 UPDATE SET TARGET.MyDate = SOURCE.MyDate
 WHEN NOT MATCHED BY TARGET THEN 
 INSERT (PK, MyDate) 
 VALUES (SOURCE.PK,SOURCE.MyDate);
END
GO
insert into Inspections(Mydate) values('01/10/2016')
select * from Hydrants
update Inspections set Mydate = '02/10/2016' where PK =1
select * from Hydrants

• user19300: How does the trigger know which record in A to insert into? Table A has existing records. Is the PK=1 doing that?
• Sting: If I understand what you're asking, the special [inserted] table within the trigger only contains the records that were either Inserted or updated from table A--and nothing else. There's also another table called [deleted]. Both [inserted] and [deleted] tables exist only within the context of triggers.
• user19300: Ok, this is starting to make sense! Thanks for the patience and information. I'll try to put this together on my end and respond back.
• Sting: @user19300 sounds good. Let us know if you've made some real progress!