TSql Trigger To Delete Rows from Table1 Upon Insert to Table2
up vote
0
down vote
favorite
I've read and tried other posts/answers but am having a hard time getting this to work. I feel like it should be pretty simple for an experienced SQL programmer, but for me I'm ready to ask for help.
When I insert a record into the HistoricalWorkorders table, I simply want to delete any records with the same workorder # in the ActiveWorkorders table.
I'm trying to accomplish this using a trigger on insert.
I started simple by using the following code to insert into ActiveWorkorders every time a record is inserted into HistoricalWorkorders. That code works.
How do I modify this query such that upon insert to the
HistoricalWorkorderstable, any records with the sameworkorder #within theActiveWorkorderstable get deleted?
On a seperate note, everyone says you can reference the inserted record simply by referencing 'inserted', but it doesn't work for me? For example, shouldn't inserted.workorder return the workorder value being inserted to the table?
ALTER trigger [dbo].[WOArchive] on [dbo].[HistoricalWorkOrders]
after update,insert
as
begin
insert into ActiveWorkorders
(Workorder,Grindcount,Status)
select i.workorder, i.grindcount, i.status
from HistoricalWorkorders t
inner join inserted i on t.workorder=i.workorder
end
sql
sql-server edited Nov 21 at 8:58
Sk83r1l4m4
458
asked Nov 20 at 16:15
Chris
1316
add a comment |
up vote
0
down vote
favorite
I've read and tried other posts/answers but am having a hard time getting this to work. I feel like it should be pretty simple for an experienced SQL programmer, but for me I'm ready to ask for help.
When I insert a record into the HistoricalWorkorders table, I simply want to delete any records with the same workorder # in the ActiveWorkorders table.
I'm trying to accomplish this using a trigger on insert.
I started simple by using the following code to insert into ActiveWorkorders every time a record is inserted into HistoricalWorkorders. That code works.
How do I modify this query such that upon insert to the
HistoricalWorkorderstable, any records with the sameworkorder #within theActiveWorkorderstable get deleted?
On a seperate note, everyone says you can reference the inserted record simply by referencing 'inserted', but it doesn't work for me? For example, shouldn't inserted.workorder return the workorder value being inserted to the table?
ALTER trigger [dbo].[WOArchive] on [dbo].[HistoricalWorkOrders]
after update,insert
as
begin
insert into ActiveWorkorders
(Workorder,Grindcount,Status)
select i.workorder, i.grindcount, i.status
from HistoricalWorkorders t
inner join inserted i on t.workorder=i.workorder
end
sql
sql-server edited Nov 21 at 8:58
Sk83r1l4m4
458
asked Nov 20 at 16:15
Chris
1316
What do you mean by referencing inserted doesn't work? I am wondering what you are trying to do because currently this will insert a new row to ActiveWorkorders every time a row is inserted or updated in HistoricalWorkOrders. Just going on the names something seems a little bit off there. It would be helpful if you could include ddl for these two tables.
– Sean Lange
Nov 20 at 16:31
Possible duplicate of SQL Trigger to Delete records in one table after insert on a different table
– GuidoG
Nov 20 at 16:34
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I've read and tried other posts/answers but am having a hard time getting this to work. I feel like it should be pretty simple for an experienced SQL programmer, but for me I'm ready to ask for help.
When I insert a record into the HistoricalWorkorders table, I simply want to delete any records with the same workorder # in the ActiveWorkorders table.
I'm trying to accomplish this using a trigger on insert.
I started simple by using the following code to insert into ActiveWorkorders every time a record is inserted into HistoricalWorkorders. That code works.
How do I modify this query such that upon insert to the
HistoricalWorkorderstable, any records with the sameworkorder #within theActiveWorkorderstable get deleted?
On a seperate note, everyone says you can reference the inserted record simply by referencing 'inserted', but it doesn't work for me? For example, shouldn't inserted.workorder return the workorder value being inserted to the table?
ALTER trigger [dbo].[WOArchive] on [dbo].[HistoricalWorkOrders]
after update,insert
as
begin
insert into ActiveWorkorders
(Workorder,Grindcount,Status)
select i.workorder, i.grindcount, i.status
from HistoricalWorkorders t
inner join inserted i on t.workorder=i.workorder
end
sql
sql-server edited Nov 21 at 8:58
Sk83r1l4m4
458
asked Nov 20 at 16:15
Chris
1316
I've read and tried other posts/answers but am having a hard time getting this to work. I feel like it should be pretty simple for an experienced SQL programmer, but for me I'm ready to ask for help.
When I insert a record into the HistoricalWorkorders table, I simply want to delete any records with the same workorder # in the ActiveWorkorders table.
I'm trying to accomplish this using a trigger on insert.
I started simple by using the following code to insert into ActiveWorkorders every time a record is inserted into HistoricalWorkorders. That code works.
How do I modify this query such that upon insert to the
HistoricalWorkorderstable, any records with the sameworkorder #within theActiveWorkorderstable get deleted?
On a seperate note, everyone says you can reference the inserted record simply by referencing 'inserted', but it doesn't work for me? For example, shouldn't inserted.workorder return the workorder value being inserted to the table?
ALTER trigger [dbo].[WOArchive] on [dbo].[HistoricalWorkOrders]
after update,insert
as
begin
insert into ActiveWorkorders
(Workorder,Grindcount,Status)
select i.workorder, i.grindcount, i.status
from HistoricalWorkorders t
inner join inserted i on t.workorder=i.workorder
end
sql
sql-server sql
sql-server edited Nov 21 at 8:58
Sk83r1l4m4
458
asked Nov 20 at 16:15
Chris
1316
edited Nov 21 at 8:58
Sk83r1l4m4
458
asked Nov 20 at 16:15
Chris
1316
edited Nov 21 at 8:58
Sk83r1l4m4
458
edited Nov 21 at 8:58
Sk83r1l4m4
458
edited Nov 21 at 8:58
Sk83r1l4m4
458
458
asked Nov 20 at 16:15
Chris
1316
asked Nov 20 at 16:15
Chris
1316
asked Nov 20 at 16:15
Chris
1316
1316
What do you mean by referencing inserted doesn't work? I am wondering what you are trying to do because currently this will insert a new row to ActiveWorkorders every time a row is inserted or updated in HistoricalWorkOrders. Just going on the names something seems a little bit off there. It would be helpful if you could include ddl for these two tables.
– Sean Lange
Nov 20 at 16:31
Possible duplicate of SQL Trigger to Delete records in one table after insert on a different table
– GuidoG
Nov 20 at 16:34
add a comment |
What do you mean by referencing inserted doesn't work? I am wondering what you are trying to do because currently this will insert a new row to ActiveWorkorders every time a row is inserted or updated in HistoricalWorkOrders. Just going on the names something seems a little bit off there. It would be helpful if you could include ddl for these two tables.
– Sean Lange
Nov 20 at 16:31
Possible duplicate of SQL Trigger to Delete records in one table after insert on a different table
– GuidoG
Nov 20 at 16:34
What do you mean by referencing inserted doesn't work? I am wondering what you are trying to do because currently this will insert a new row to ActiveWorkorders every time a row is inserted or updated in HistoricalWorkOrders. Just going on the names something seems a little bit off there. It would be helpful if you could include ddl for these two tables.
– Sean Lange
Nov 20 at 16:31
What do you mean by referencing inserted doesn't work? I am wondering what you are trying to do because currently this will insert a new row to ActiveWorkorders every time a row is inserted or updated in HistoricalWorkOrders. Just going on the names something seems a little bit off there. It would be helpful if you could include ddl for these two tables.
– Sean Lange
Nov 20 at 16:31
Possible duplicate of SQL Trigger to Delete records in one table after insert on a different table
– GuidoG
Nov 20 at 16:34
Possible duplicate of SQL Trigger to Delete records in one table after insert on a different table
– GuidoG
Nov 20 at 16:34
add a comment |
active
oldest
votes
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%2f53397165%2ftsql-trigger-to-delete-rows-from-table1-upon-insert-to-table2%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
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%2fstackoverflow.com%2fquestions%2f53397165%2ftsql-trigger-to-delete-rows-from-table1-upon-insert-to-table2%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
What do you mean by referencing inserted doesn't work? I am wondering what you are trying to do because currently this will insert a new row to ActiveWorkorders every time a row is inserted or updated in HistoricalWorkOrders. Just going on the names something seems a little bit off there. It would be helpful if you could include ddl for these two tables.
– Sean Lange
Nov 20 at 16:31
Possible duplicate of SQL Trigger to Delete records in one table after insert on a different table
– GuidoG
Nov 20 at 16:34