2

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.

4
  • I recommend doing it in your code, not depending on FOREIGN KEYs to do all the subtle/complex things that you need. There should be an API for anything complex (like deleting a Project), not just a DELETE statement.
    – Rick James
    Dec 22, 2018 at 16:38
  • You can define your FK as ON DELETE CASCADE. This will take care of deleting the child records when parent record is deleted.
    – Kin Shah
    Dec 22, 2018 at 16:52
  • @Kin they want to delete from ConstructorEmployee. There is no FK from that table to Project. Dec 22, 2018 at 16:54
  • 1
    Ok .. I see that. May be a background purge job ran on a daily basis ?
    – Kin Shah
    Dec 22, 2018 at 16:59

1 Answer 1

6

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.