SQL Server : delete row trigger
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
andEID
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
New contributor
add a comment |
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
andEID
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
New contributor
I recommend doing it in your code, not depending onFOREIGN 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 aDELETE
statement.
– Rick James
Dec 22 at 16:38
You can define your FK asON 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 fromConstructorEmployee
. There is no FK from that table toProject
.
– 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
add a comment |
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
andEID
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
New contributor
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
andEID
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
sql-server trigger sql-server-2017
New contributor
New contributor
edited yesterday
marc_s
6,99053749
6,99053749
New contributor
asked Dec 22 at 15:03
user168696
111
111
New contributor
New contributor
I recommend doing it in your code, not depending onFOREIGN 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 aDELETE
statement.
– Rick James
Dec 22 at 16:38
You can define your FK asON 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 fromConstructorEmployee
. There is no FK from that table toProject
.
– 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
add a comment |
I recommend doing it in your code, not depending onFOREIGN 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 aDELETE
statement.
– Rick James
Dec 22 at 16:38
You can define your FK asON 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 fromConstructorEmployee
. There is no FK from that table toProject
.
– 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
add a comment |
1 Answer
1
active
oldest
votes
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.
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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.
add a comment |
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.
add a comment |
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.
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.
edited Dec 22 at 17:52
answered Dec 22 at 17:09
McNets
14.7k41857
14.7k41857
add a comment |
add a comment |
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.
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
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 aDELETE
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 toProject
.– 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