Sql-server – Creating SQL Server triggers to copy a record from one table and populate another

sql serversql-server-2008-r2trigger

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?

Best Answer

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
Related Question