Cfxtrjtrk

I have these 3 tables:

ConstructorEmployee

• 
  EID (employee ID) as primary key and other key.

Project

• 
  PID as primary key and other keys.

ProjectConstructorEmployee

• 
  PID and EID as foreign keys to the other tables.

Each ConstructorEmployee can work on several Projects. I need to create a trigger that if project has been deleted, I need to delete all ConstructorEmployee that worked only on this project. I need to delete them from the ConstructorEmployee table.

I'm working on SQL Server 2017.

I assume your table schema is similar to next one:

CREATE TABLE ConstructorEmployee 

(

    EID int PRIMARY KEY

);



CREATE TABLE Project 

(

    PID int PRIMARY KEY

);



CREATE TABLE ProjectConstructorEmployee 

(

    PID int, 

    EID int, 

    CONSTRAINT PK_PCE PRIMARY KEY(PID, EID),

    CONSTRAINT FK_P FOREIGN KEY (PID) REFERENCES Project (PID),

    CONSTRAINT FK_CE FOREIGN KEY (EID) REFERENCES ConstructorEmployee (EID)

);

GO

With two FOREIGN KEY on ProjectConstructorEmployee table.

Now let me add some data 3 employes and 3 projects, but Employee=1 has worked only on the first Project.

INSERT INTO ConstructorEmployee VALUES (1), (2),(3);  

GO



INSERT INTO Project VALUES (1), (2), (3);

GO



-- EID = 1 worked in PID 1 only

INSERT INTO ProjectConstructorEmployee VALUES

(1, 1), (1, 2), (2, 2), (2, 3), (3, 2), (3, 3);

GO

Next query returns a list of employees that has worked in only one project, in this case PID=1

-- Employees working on PID=1 that didn't work in any other project

SELECT EID

FROM   ProjectConstructorEmployee

WHERE  EID IN (SELECT EID FROM ProjectConstructorEmployee WHERE PID=1)

GROUP BY EID

HAVING COUNT(*) = 1

GO



| EID |

| --: |

|   1 |

I need to create a trigger that...

No, don't use a trigger, let me suggest to use an STORED PROCEDURE, it's always more clear for your and for any further developer. Or if you want to use a trigger, use a BEFORE DELETE trigger that calls this procedure.

CREATE PROCEDURE DeleteProject(@PID int)

AS

BEGIN

    BEGIN TRY



        DECLARE @employee TABLE (EID int);



        BEGIN TRANSACTION;



        -- save a list of employees that only worked in this proejct

        -- due to FOREIGN KEYS you can't delete ConstructorEmployee table

        -- until you have deleted the other both.

        INSERT INTO @employee

        SELECT EID

        FROM   ProjectConstructorEmployee

        WHERE  EID IN (SELECT EID 

                       FROM ProjectConstructorEmployee 

                       WHERE PID = @PID)

        GROUP BY EID

        HAVING COUNT(*) = 1;                   



        DELETE FROM ProjectConstructorEmployee

        WHERE  PID = @PID;



        DELETE FROM Project

        WHERE  PID = @PID;



        DELETE FROM ConstructorEmployee

        WHERE EID IN (SELECT EID FROM @employee);



        IF @@TRANCOUNT > 0

            COMMIT TRANSACTION;



    END TRY

    BEGIN CATCH



        IF @@TRANCOUNT > 0

            ROLLBACK TRANSACTION;



        -- Your error handler



        THROW;



    END CATCH

END

GO

Ok, let me try it by deleting projects 1 and 2:

EXEC DeleteProject @PID = 1;

EXEC DeleteProject @PID = 2;



SELECT * FROM ConstructorEmployee;

SELECT * FROM Project;

SELECT * FROM ProjectConstructorEmployee;

GO

Remaining employees:

| EID |

| --: |

|   2 |

|   3 |

Projects:

| PID |

| --: |

|   3 |

and ProjectsEmployees:

PID | EID

--: | --:

  3 |   2

  3 |   3

db<>fiddle here

But...are you sure you want to delete all this information?

You will lost all the Project's history, employees and projects will disappear of your database. Maybe you could set a flag like Active(Yes/No) a let this information available for whichever want to recover later.