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?

does this answer help ? – Kin Shah Jun 08 '16 at 14:43 — Kin Shah, Jun 08 '16 at 14:43

Sting · Answer 1 · 2016-06-08T20:26:40.643

4

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

edited Jun 08 '16 at 20:26

answered Jun 08 '16 at 14:50

Sting

2,788
11
20

1

How does the trigger know which record in A to insert into? Table A has existing records. Is the PK=1 doing that? – user19300 Jun 08 '16 at 16:01
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. – Sting Jun 08 '16 at 16:16
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. – user19300 Jun 09 '16 at 20:22
@user19300 sounds good. Let us know if you've made some real progress! – Sting Jun 10 '16 at 14:58

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

1 Answers1