CODEWITHSUNDEEP
sqlsql-servert-sql
Michael
Michael

Reputation: 13616

How to write trigger to update row in another table?

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?

Upvotes: 1

Views: 271

Answers (2)

marc_s
marc_s

Reputation: 754220

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.

Upvotes: 1

Reboon
Reboon

Reputation: 578

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.

Upvotes: 0

Related Questions