SQL Server on delete trigger not doing what I expect it to do
Trigger should fire when deleting a dish. All orders containing this dish are going to be deleted.
So far, I've got this script:
create trigger OnDishDeleted
on Dish
after delete as
begin
delete from Dish_Order
where Dish_ID = (select Dish_ID from deleted)
end
When I try to delete a dish, I get an error:
The DELETE statement conflicted with the REFERENCE constraint "FK_Dish_Order_Dish". The conflict occurred in database "davay_rabotai2", table "dbo.Dish_Order", column 'Dish_ID'.
ER diagram:
Click!
sql-server triggers database-trigger edited Nov 26 '18 at 11:44
marc_s
583k13011241270
asked Nov 26 '18 at 11:29
KasKas
336
add a comment |
Trigger should fire when deleting a dish. All orders containing this dish are going to be deleted.
So far, I've got this script:
create trigger OnDishDeleted
on Dish
after delete as
begin
delete from Dish_Order
where Dish_ID = (select Dish_ID from deleted)
end
When I try to delete a dish, I get an error:
The DELETE statement conflicted with the REFERENCE constraint "FK_Dish_Order_Dish". The conflict occurred in database "davay_rabotai2", table "dbo.Dish_Order", column 'Dish_ID'.
ER diagram:
Click!
sql-server triggers database-trigger edited Nov 26 '18 at 11:44
marc_s
583k13011241270
asked Nov 26 '18 at 11:29
KasKas
336
2
Why not turn on cascading delete on the foreign key instead? If you want to do this via trigger, you have to remove the FK, because constraints checks happen well before this trigger fires.
– Damien_The_Unbeliever
Nov 26 '18 at 11:33
2
This trigger assumes that only 1 row will ever be deleted at a time (if more than one is, then it'll give an error about the subquery having more than 1 row). But @Damien_The_Unbeliever is right, Cascade delete would seem the far simpler solution over a trigger.
– Larnu
Nov 26 '18 at 11:34
@Damien_The_Unbeliever the problem here is that this is my task at university. I should do it by using triggers. Is there any other ways to remake this ER diagram?
– Kas
Nov 26 '18 at 11:48
1
@Kas make your trigger notafter delete, butinstead of delete. Then delete from Dish_Order first, and then delete the row(s) from Dish table.
– Andrey Nikolov
Nov 26 '18 at 11:52
OMG when are they going to stop teaching useless triggers at university. Triggers are a last resort!!
– Nick.McDermaid
Nov 26 '18 at 14:11
add a comment |
Trigger should fire when deleting a dish. All orders containing this dish are going to be deleted.
So far, I've got this script:
create trigger OnDishDeleted
on Dish
after delete as
begin
delete from Dish_Order
where Dish_ID = (select Dish_ID from deleted)
end
When I try to delete a dish, I get an error:
The DELETE statement conflicted with the REFERENCE constraint "FK_Dish_Order_Dish". The conflict occurred in database "davay_rabotai2", table "dbo.Dish_Order", column 'Dish_ID'.
ER diagram:
Click!
sql-server triggers database-trigger edited Nov 26 '18 at 11:44
marc_s
583k13011241270
asked Nov 26 '18 at 11:29
KasKas
336
Trigger should fire when deleting a dish. All orders containing this dish are going to be deleted.
So far, I've got this script:
create trigger OnDishDeleted
on Dish
after delete as
begin
delete from Dish_Order
where Dish_ID = (select Dish_ID from deleted)
end
When I try to delete a dish, I get an error:
The DELETE statement conflicted with the REFERENCE constraint "FK_Dish_Order_Dish". The conflict occurred in database "davay_rabotai2", table "dbo.Dish_Order", column 'Dish_ID'.
ER diagram:
Click!
sql-server triggers database-trigger sql-server triggers database-trigger edited Nov 26 '18 at 11:44
marc_s
583k13011241270
asked Nov 26 '18 at 11:29
KasKas
336
edited Nov 26 '18 at 11:44
marc_s
583k13011241270
asked Nov 26 '18 at 11:29
KasKas
336
edited Nov 26 '18 at 11:44
marc_s
583k13011241270
edited Nov 26 '18 at 11:44
marc_s
583k13011241270
edited Nov 26 '18 at 11:44
marc_s
583k13011241270
583k13011241270
asked Nov 26 '18 at 11:29
KasKas
336
asked Nov 26 '18 at 11:29
KasKas
336
asked Nov 26 '18 at 11:29
KasKas
336
336
2
Why not turn on cascading delete on the foreign key instead? If you want to do this via trigger, you have to remove the FK, because constraints checks happen well before this trigger fires.
– Damien_The_Unbeliever
Nov 26 '18 at 11:33
2
This trigger assumes that only 1 row will ever be deleted at a time (if more than one is, then it'll give an error about the subquery having more than 1 row). But @Damien_The_Unbeliever is right, Cascade delete would seem the far simpler solution over a trigger.
– Larnu
Nov 26 '18 at 11:34
@Damien_The_Unbeliever the problem here is that this is my task at university. I should do it by using triggers. Is there any other ways to remake this ER diagram?
– Kas
Nov 26 '18 at 11:48
1
@Kas make your trigger notafter delete, butinstead of delete. Then delete from Dish_Order first, and then delete the row(s) from Dish table.
– Andrey Nikolov
Nov 26 '18 at 11:52
OMG when are they going to stop teaching useless triggers at university. Triggers are a last resort!!
– Nick.McDermaid
Nov 26 '18 at 14:11
add a comment |
2
Why not turn on cascading delete on the foreign key instead? If you want to do this via trigger, you have to remove the FK, because constraints checks happen well before this trigger fires.
– Damien_The_Unbeliever
Nov 26 '18 at 11:33
2
This trigger assumes that only 1 row will ever be deleted at a time (if more than one is, then it'll give an error about the subquery having more than 1 row). But @Damien_The_Unbeliever is right, Cascade delete would seem the far simpler solution over a trigger.
– Larnu
Nov 26 '18 at 11:34
@Damien_The_Unbeliever the problem here is that this is my task at university. I should do it by using triggers. Is there any other ways to remake this ER diagram?
– Kas
Nov 26 '18 at 11:48
1
@Kas make your trigger notafter delete, butinstead of delete. Then delete from Dish_Order first, and then delete the row(s) from Dish table.
– Andrey Nikolov
Nov 26 '18 at 11:52
OMG when are they going to stop teaching useless triggers at university. Triggers are a last resort!!
– Nick.McDermaid
Nov 26 '18 at 14:11
2
2
Why not turn on cascading delete on the foreign key instead? If you want to do this via trigger, you have to remove the FK, because constraints checks happen well before this trigger fires.
– Damien_The_Unbeliever
Nov 26 '18 at 11:33
Why not turn on cascading delete on the foreign key instead? If you want to do this via trigger, you have to remove the FK, because constraints checks happen well before this trigger fires.
– Damien_The_Unbeliever
Nov 26 '18 at 11:33
2
2
This trigger assumes that only 1 row will ever be deleted at a time (if more than one is, then it'll give an error about the subquery having more than 1 row). But @Damien_The_Unbeliever is right, Cascade delete would seem the far simpler solution over a trigger.
– Larnu
Nov 26 '18 at 11:34
This trigger assumes that only 1 row will ever be deleted at a time (if more than one is, then it'll give an error about the subquery having more than 1 row). But @Damien_The_Unbeliever is right, Cascade delete would seem the far simpler solution over a trigger.
– Larnu
Nov 26 '18 at 11:34
@Damien_The_Unbeliever the problem here is that this is my task at university. I should do it by using triggers. Is there any other ways to remake this ER diagram?
– Kas
Nov 26 '18 at 11:48
@Damien_The_Unbeliever the problem here is that this is my task at university. I should do it by using triggers. Is there any other ways to remake this ER diagram?
– Kas
Nov 26 '18 at 11:48
1
1
@Kas make your trigger not
after delete, but instead of delete. Then delete from Dish_Order first, and then delete the row(s) from Dish table.– Andrey Nikolov
Nov 26 '18 at 11:52
@Kas make your trigger not
after delete, but instead of delete. Then delete from Dish_Order first, and then delete the row(s) from Dish table.– Andrey Nikolov
Nov 26 '18 at 11:52
OMG when are they going to stop teaching useless triggers at university. Triggers are a last resort!!
– Nick.McDermaid
Nov 26 '18 at 14:11
OMG when are they going to stop teaching useless triggers at university. Triggers are a last resort!!
– Nick.McDermaid
Nov 26 '18 at 14:11
add a comment |
1 Answer
1
active
oldest
votes
Use an instead of trigger if you need to delete rows in another table first.
This is an example that can get you started.
CREATE trigger OnDishDeleted on dbo.Dish
instead of delete
as
begin
set nocount on
delete from Dish_Order where Dish_ID in (select Dish_ID from deleted)
delete from Dish where Dish_ID in (select Dish_ID from deleted)
end
The difference with a normal trigger is that you also need to delete the rows from the table Dish yourself in this trigger.
An instead of trigger does not deletes anything, it just fires when the delete statement is given and you have to do all the work yourself.
More info
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-2017
and
https://msdn.microsoft.com/en-us/library/def01zh2.aspx
Also notice that I use IN in stead of = for the where clause, this is because deleted in a trigger can have more than one row. That is another flaw in your trigger code. Always assume there can be more then one row in triggers.
That said, your solution has another problem.
From your ER diagram i see you also have payments and other rows stored in other tables that are linked to a dish. You also need to delete these rows.
But deleting payments is not something I would like to see happening in my database.
Are you sure this is what you want to do ?
answered Nov 26 '18 at 12:11
GuidoGGuidoG
5,84832145
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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
});
}
});
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%2fstackoverflow.com%2fquestions%2f53480153%2fsql-server-on-delete-trigger-not-doing-what-i-expect-it-to-do%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
Use an instead of trigger if you need to delete rows in another table first.
This is an example that can get you started.
CREATE trigger OnDishDeleted on dbo.Dish
instead of delete
as
begin
set nocount on
delete from Dish_Order where Dish_ID in (select Dish_ID from deleted)
delete from Dish where Dish_ID in (select Dish_ID from deleted)
end
The difference with a normal trigger is that you also need to delete the rows from the table Dish yourself in this trigger.
An instead of trigger does not deletes anything, it just fires when the delete statement is given and you have to do all the work yourself.
More info
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-2017
and
https://msdn.microsoft.com/en-us/library/def01zh2.aspx
Also notice that I use IN in stead of = for the where clause, this is because deleted in a trigger can have more than one row. That is another flaw in your trigger code. Always assume there can be more then one row in triggers.
That said, your solution has another problem.
From your ER diagram i see you also have payments and other rows stored in other tables that are linked to a dish. You also need to delete these rows.
But deleting payments is not something I would like to see happening in my database.
Are you sure this is what you want to do ?
answered Nov 26 '18 at 12:11
GuidoGGuidoG
5,84832145
add a comment |
Use an instead of trigger if you need to delete rows in another table first.
This is an example that can get you started.
CREATE trigger OnDishDeleted on dbo.Dish
instead of delete
as
begin
set nocount on
delete from Dish_Order where Dish_ID in (select Dish_ID from deleted)
delete from Dish where Dish_ID in (select Dish_ID from deleted)
end
The difference with a normal trigger is that you also need to delete the rows from the table Dish yourself in this trigger.
An instead of trigger does not deletes anything, it just fires when the delete statement is given and you have to do all the work yourself.
More info
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-2017
and
https://msdn.microsoft.com/en-us/library/def01zh2.aspx
Also notice that I use IN in stead of = for the where clause, this is because deleted in a trigger can have more than one row. That is another flaw in your trigger code. Always assume there can be more then one row in triggers.
That said, your solution has another problem.
From your ER diagram i see you also have payments and other rows stored in other tables that are linked to a dish. You also need to delete these rows.
But deleting payments is not something I would like to see happening in my database.
Are you sure this is what you want to do ?
answered Nov 26 '18 at 12:11
GuidoGGuidoG
5,84832145
add a comment |
Use an instead of trigger if you need to delete rows in another table first.
This is an example that can get you started.
CREATE trigger OnDishDeleted on dbo.Dish
instead of delete
as
begin
set nocount on
delete from Dish_Order where Dish_ID in (select Dish_ID from deleted)
delete from Dish where Dish_ID in (select Dish_ID from deleted)
end
The difference with a normal trigger is that you also need to delete the rows from the table Dish yourself in this trigger.
An instead of trigger does not deletes anything, it just fires when the delete statement is given and you have to do all the work yourself.
More info
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-2017
and
https://msdn.microsoft.com/en-us/library/def01zh2.aspx
Also notice that I use IN in stead of = for the where clause, this is because deleted in a trigger can have more than one row. That is another flaw in your trigger code. Always assume there can be more then one row in triggers.
That said, your solution has another problem.
From your ER diagram i see you also have payments and other rows stored in other tables that are linked to a dish. You also need to delete these rows.
But deleting payments is not something I would like to see happening in my database.
Are you sure this is what you want to do ?
answered Nov 26 '18 at 12:11
GuidoGGuidoG
5,84832145
Use an instead of trigger if you need to delete rows in another table first.
This is an example that can get you started.
CREATE trigger OnDishDeleted on dbo.Dish
instead of delete
as
begin
set nocount on
delete from Dish_Order where Dish_ID in (select Dish_ID from deleted)
delete from Dish where Dish_ID in (select Dish_ID from deleted)
end
The difference with a normal trigger is that you also need to delete the rows from the table Dish yourself in this trigger.
An instead of trigger does not deletes anything, it just fires when the delete statement is given and you have to do all the work yourself.
More info
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-2017
and
https://msdn.microsoft.com/en-us/library/def01zh2.aspx
Also notice that I use IN in stead of = for the where clause, this is because deleted in a trigger can have more than one row. That is another flaw in your trigger code. Always assume there can be more then one row in triggers.
That said, your solution has another problem.
From your ER diagram i see you also have payments and other rows stored in other tables that are linked to a dish. You also need to delete these rows.
But deleting payments is not something I would like to see happening in my database.
Are you sure this is what you want to do ?
answered Nov 26 '18 at 12:11
GuidoGGuidoG
5,84832145
edited Nov 26 '18 at 12:43
answered Nov 26 '18 at 12:11
GuidoGGuidoG
5,84832145
answered Nov 26 '18 at 12:11
GuidoGGuidoG
5,84832145
answered Nov 26 '18 at 12:11
GuidoGGuidoG
5,84832145
5,84832145
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f53480153%2fsql-server-on-delete-trigger-not-doing-what-i-expect-it-to-do%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
2
Why not turn on cascading delete on the foreign key instead? If you want to do this via trigger, you have to remove the FK, because constraints checks happen well before this trigger fires.
– Damien_The_Unbeliever
Nov 26 '18 at 11:33
2
This trigger assumes that only 1 row will ever be deleted at a time (if more than one is, then it'll give an error about the subquery having more than 1 row). But @Damien_The_Unbeliever is right, Cascade delete would seem the far simpler solution over a trigger.
– Larnu
Nov 26 '18 at 11:34
@Damien_The_Unbeliever the problem here is that this is my task at university. I should do it by using triggers. Is there any other ways to remake this ER diagram?
– Kas
Nov 26 '18 at 11:48
1
@Kas make your trigger not
after delete, butinstead of delete. Then delete from Dish_Order first, and then delete the row(s) from Dish table.– Andrey Nikolov
Nov 26 '18 at 11:52
OMG when are they going to stop teaching useless triggers at university. Triggers are a last resort!!
– Nick.McDermaid
Nov 26 '18 at 14:11