Sql-server – Create a trigger to fill a field according to acondition in sql

conditionsql-server-2012trigger

I want to create a trigger to fill a field according to acondition as below description

For example:
I have a table for all insps that have attachments named: “inspectionAttachments” and I have another table “inspections” that contains all insps and I want to fill for example column/field named “shop” in the inspections table to define which insps that have attachments or not
So, I want to fill the shop field with Yes if at least this insp has one attachment and fill this field with No if there is no attachement

How I can write the trigger to make what I want

Thanks for any help ?

enter image description here

Best,
Lubna

Best Answer

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