Sql-server – SQL Server : delete row trigger

sql serversql-server-2017trigger

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.

Best Answer

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.

Related Question