Create a trigger to fill a field according to acondition in sql
The following solution assumes rows will be added to the inspections table first (with a default value of NO in the Shop column. It is also assumed that only one attachment can exists for a given inspectionid
Two triggers have been defined on the attachments table.
The insert trigger on the attachments table will update the inspections table with YES in the Shop column to reflect the added attachment.
The delete trigger on the attachments table will update the inspections table with NO in the Shop column to reflect the deleted attachment.
--demo set up
DROP TABLE IF EXISTS Inspections;
DROP TABLE IF EXISTS Attachments;
go
    CREATE TABLE Inspections (
    InspectionId INT
    ,Shop CHAR(5)
    );
GO

CREATE TABLE Attachments (
    InspectionId INT
    ,Attachment CHAR(5)
    );
GO

--create insert trigger 
CREATE TRIGGER Attachments_Insert ON Attachments
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE i    --attachment found for inspection
    SET i.Shop = 'YES'
    FROM Inspections i
    JOIN inserted ins ON ins.InspectionId = i.InspectionId
END;
GO
--create delete trigger 
CREATE TRIGGER Attachments_Delete ON Attachments
AFTER DELETE
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE i    --attachment deleted for inspection
    SET i.Shop = 'NO'
    FROM Inspections i
    JOIN deleted del ON del.InspectionId = i.InspectionId

END;
GO

--test trigger
--insert test rows into inspections table with default of NO in Shop column
INSERT INTO Inspections (InspectionId,Shop)
VALUES (1,'NO');

INSERT INTO Inspections (InspectionId,Shop)
VALUES (2,'NO');

--insert row into attachement
INSERT INTO Attachments (
    InspectionId
    ,Attachment
    )
VALUES (
    1
    ,'xyz'
    );

--verify results - one inspection row should have YES for Shop column
SELECT *
FROM Inspections;

--testing delete of attachment - should update existing inspection to NO
delete from Attachments where InspectionId=1

--verify results
SELECT *
FROM Inspections;