SQL Server : delete row trigger












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.










share|improve this question









New contributor




user168696 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.




















  • 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 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
    Dec 22 at 16:52










  • @Kin they want to delete from ConstructorEmployee. There is no FK from that table to Project.
    – yper-crazyhat-cubeᵀᴹ
    Dec 22 at 16:54








  • 1




    Ok .. I see that. May be a background purge job ran on a daily basis ?
    – Kin
    Dec 22 at 16:59


















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.










share|improve this question









New contributor




user168696 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.




















  • 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 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
    Dec 22 at 16:52










  • @Kin they want to delete from ConstructorEmployee. There is no FK from that table to Project.
    – yper-crazyhat-cubeᵀᴹ
    Dec 22 at 16:54








  • 1




    Ok .. I see that. May be a background purge job ran on a daily basis ?
    – Kin
    Dec 22 at 16:59
















2












2








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.










share|improve this question









New contributor




user168696 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.











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.







sql-server trigger sql-server-2017






share|improve this question









New contributor




user168696 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.











share|improve this question









New contributor




user168696 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.









share|improve this question




share|improve this question








edited yesterday









marc_s

6,99053749




6,99053749






New contributor




user168696 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.









asked Dec 22 at 15:03









user168696

111




111




New contributor




user168696 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.





New contributor





user168696 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.






user168696 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.












  • 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 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
    Dec 22 at 16:52










  • @Kin they want to delete from ConstructorEmployee. There is no FK from that table to Project.
    – yper-crazyhat-cubeᵀᴹ
    Dec 22 at 16:54








  • 1




    Ok .. I see that. May be a background purge job ran on a daily basis ?
    – Kin
    Dec 22 at 16:59




















  • 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 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
    Dec 22 at 16:52










  • @Kin they want to delete from ConstructorEmployee. There is no FK from that table to Project.
    – yper-crazyhat-cubeᵀᴹ
    Dec 22 at 16:54








  • 1




    Ok .. I see that. May be a background purge job ran on a daily basis ?
    – Kin
    Dec 22 at 16:59


















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 at 16:38




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 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
Dec 22 at 16:52




You can define your FK as ON DELETE CASCADE. This will take care of deleting the child records when parent record is deleted.
– Kin
Dec 22 at 16:52












@Kin they want to delete from ConstructorEmployee. There is no FK from that table to Project.
– yper-crazyhat-cubeᵀᴹ
Dec 22 at 16:54






@Kin they want to delete from ConstructorEmployee. There is no FK from that table to Project.
– yper-crazyhat-cubeᵀᴹ
Dec 22 at 16:54






1




1




Ok .. I see that. May be a background purge job ran on a daily basis ?
– Kin
Dec 22 at 16:59






Ok .. I see that. May be a background purge job ran on a daily basis ?
– Kin
Dec 22 at 16:59












1 Answer
1






active

oldest

votes


















5














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.






share|improve this answer























    Your Answer








    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "182"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: false,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: null,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });






    user168696 is a new contributor. Be nice, and check out our Code of Conduct.










    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f225618%2fsql-server-delete-row-trigger%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    5














    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.






    share|improve this answer




























      5














      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.






      share|improve this answer


























        5












        5








        5






        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.






        share|improve this 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.







        share|improve this answer














        share|improve this answer



        share|improve this answer








        edited Dec 22 at 17:52

























        answered Dec 22 at 17:09









        McNets

        14.7k41857




        14.7k41857






















            user168696 is a new contributor. Be nice, and check out our Code of Conduct.










            draft saved

            draft discarded


















            user168696 is a new contributor. Be nice, and check out our Code of Conduct.













            user168696 is a new contributor. Be nice, and check out our Code of Conduct.












            user168696 is a new contributor. Be nice, and check out our Code of Conduct.
















            Thanks for contributing an answer to Database Administrators Stack Exchange!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.





            Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


            Please pay close attention to the following guidance:


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f225618%2fsql-server-delete-row-trigger%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            數位音樂下載

            When can things happen in Etherscan, such as the picture below?

            格利澤436b