How to write trigger to update row in another table?

Question

I have two tables:

CREATE TABLE EventsCnfig
(
Id int,
InspectionId int,
Event int
);

And this:

CREATE TABLE Inspections
(
Id int,
IsRepaired Bit
);

InspectionId in EventsCnfig table is foreign key of Inspections table with relation of one to many.

Here is SQL plunker

I need to create trigger when any row in EventsCnfig table updated the value of the column Event to -1 or inserted new row with the Event value -1 the row in Inspections table with appropriate Id has to update IsRepaired value in column to 1(true).

How can I write the trigger to implement the desired logic?

Write the trigger as best you can and we will help.what have you tried so far? — nicomp, May 20 '16 at 23:14

score 1 · Accepted Answer · answered May 21 '16 at 06:43

I would write two triggers - one for UPDATE, another for INSERT - if you try to do this in a single trigger, the code gets messy because of the checks for "is this an INSERT or UPDATE operation?" etc. - don't do that....

AFTER UPDATE trigger:

CREATE TRIGGER dbo.TrgEventsConfigUpdate
ON dbo.EventsConfig
AFTER UPDATE
AS 
    UPDATE insp
    SET IsRepaired = 1
    FROM dbo.Inspections insp
    INNER JOIN Inserted i ON i.InspectionId = insp.Id
    INNER JOIN Deleted d ON d.Id = i.Id
    WHERE i.[Event] = -1 AND d.[Event] <> -1

Basically, after an update, you need to look at the Inserted and Deleted pseudo tables which contain the updated rows - if the new row (after the update) has a value of -1, while the old row (before the update) did not --> then the column Event has been updated to -1 and thus the IsRepaired in the table Inspections needs to be set to 1 (true).

AFTER INSERT trigger:

CREATE TRIGGER dbo.TrgEventsConfigInsert
ON dbo.EventsConfig
AFTER INSERT
AS 
    UPDATE insp
    SET IsRepaired = 1
    FROM dbo.Inspections insp
    INNER JOIN Inserted i ON i.InspectionId = insp.Id
    WHERE i.[Event] = -1

Same idea - just a bit simpler, since there's no "old" row to compare to: if the column in the list of inserted rows has a value of -1, then update the Inspections table for those InspectionId values to be 1.

Mark, thanks for the answer. In SQL fiddle when I try to create insert and update triggers I get error: Incorrect syntax near the keyword 'TRIGGER'. I tryed END; but still I get error.So any idea how can I create multiple triggers in sql fiddle? — Michael, May 21 '16 at 10:11

score 0 · Answer 2 · edited May 23 '17 at 12:16

Here you can read all about triggers:

How to use update trigger to update another table?

https://msdn.microsoft.com/en-us/library/ms189799.aspx

http://www.sqlteam.com/article/an-introduction-to-triggers-part-i

There should be more than enough information to build the trigger by yourself. If you have a "finished" trigger that does not work the way you want, you can post it here and the community will help you out.

How to write trigger to update row in another table?

2 Answers2