SQL - trigger select into after update/insert
1
i have a table called Audit_Data, and from time to time there is an update coming. Every single update consists of around 300 rows (around 20 columns per row), and all of the rows from the same update share the same Audit_ID.
I have a select, that pulls out only this data, which is relevant for me. It basically transform the 300x20 field data into one row of data.
Is there a way to create a SQL trigger, that would perform the select on the updated Audit_Data table, and insert selected data into table named Audit_Final?
This is my select statement that i use to pull out the relevant data:
SELECT main.Audit_ID
,main.Item_19
,main.Item_1
,main.Item_7
,main.Item_8
,Item_17
,main.Item_13
,macaddr.Item_2
,macaddr.Item_16
,t1.Item_1
FROM dbo.[Audit_Data] AS main
LEFT JOIN
(
SELECT Audit_ID, Item_2, Item_16
FROM dbo.[Audit_Data] AS macaddr
WHERE
(Item_2 NOT LIKE 'Hyper-V%')
AND (Item_17 = 'connected')
AND (Item_18 IN ('10000Mbps', '1000MBps') OR ITEM_9 IS NOT NULL AND ITEM_10 IS NOT NULL)
AND (Item_18 != '100Mbps')
) macaddr ON main.Audit_ID = macaddr.Audit_ID
LEFT JOIN
(
SELECT Audit_ID, Category_ID, Item_1, Record_ordinal
FROM dbo.[Audit_Data] AS t1
WHERE
Item_1 = 'Automatyczna konfiguracja sieci przewodowej' OR Item_1 = 'dot3svc' OR Item_1 = 'Wired AutoConfig'
AND Item_3 = 'Running'
AND Category_ID = '4100'
) t1 ON main.Audit_ID = t1.Audit_ID
WHERE
main.Record_Ordinal = '2'
ORDER BY main.Audit_ID
sql
sql-server tsql database-trigger asked Nov 13 '18 at 11:47
Mikołaj StempniewiczMikołaj Stempniewicz
115
add a comment |
1
i have a table called Audit_Data, and from time to time there is an update coming. Every single update consists of around 300 rows (around 20 columns per row), and all of the rows from the same update share the same Audit_ID.
I have a select, that pulls out only this data, which is relevant for me. It basically transform the 300x20 field data into one row of data.
Is there a way to create a SQL trigger, that would perform the select on the updated Audit_Data table, and insert selected data into table named Audit_Final?
This is my select statement that i use to pull out the relevant data:
SELECT main.Audit_ID
,main.Item_19
,main.Item_1
,main.Item_7
,main.Item_8
,Item_17
,main.Item_13
,macaddr.Item_2
,macaddr.Item_16
,t1.Item_1
FROM dbo.[Audit_Data] AS main
LEFT JOIN
(
SELECT Audit_ID, Item_2, Item_16
FROM dbo.[Audit_Data] AS macaddr
WHERE
(Item_2 NOT LIKE 'Hyper-V%')
AND (Item_17 = 'connected')
AND (Item_18 IN ('10000Mbps', '1000MBps') OR ITEM_9 IS NOT NULL AND ITEM_10 IS NOT NULL)
AND (Item_18 != '100Mbps')
) macaddr ON main.Audit_ID = macaddr.Audit_ID
LEFT JOIN
(
SELECT Audit_ID, Category_ID, Item_1, Record_ordinal
FROM dbo.[Audit_Data] AS t1
WHERE
Item_1 = 'Automatyczna konfiguracja sieci przewodowej' OR Item_1 = 'dot3svc' OR Item_1 = 'Wired AutoConfig'
AND Item_3 = 'Running'
AND Category_ID = '4100'
) t1 ON main.Audit_ID = t1.Audit_ID
WHERE
main.Record_Ordinal = '2'
ORDER BY main.Audit_ID
sql
sql-server tsql database-trigger asked Nov 13 '18 at 11:47
Mikołaj StempniewiczMikołaj Stempniewicz
115
Oh, sorry about that. Updated the initial post with the sql-server tag.
– Mikołaj Stempniewicz
Nov 13 '18 at 12:00
So all you wan is to automate that select statement to happen after every table update (and direct your output into a specific table) is that correct?
– Bartosz X
Nov 13 '18 at 12:03
@BartoszX precisely :)
– Mikołaj Stempniewicz
Nov 13 '18 at 12:15
add a comment |
1
1
1
i have a table called Audit_Data, and from time to time there is an update coming. Every single update consists of around 300 rows (around 20 columns per row), and all of the rows from the same update share the same Audit_ID.
I have a select, that pulls out only this data, which is relevant for me. It basically transform the 300x20 field data into one row of data.
Is there a way to create a SQL trigger, that would perform the select on the updated Audit_Data table, and insert selected data into table named Audit_Final?
This is my select statement that i use to pull out the relevant data:
SELECT main.Audit_ID
,main.Item_19
,main.Item_1
,main.Item_7
,main.Item_8
,Item_17
,main.Item_13
,macaddr.Item_2
,macaddr.Item_16
,t1.Item_1
FROM dbo.[Audit_Data] AS main
LEFT JOIN
(
SELECT Audit_ID, Item_2, Item_16
FROM dbo.[Audit_Data] AS macaddr
WHERE
(Item_2 NOT LIKE 'Hyper-V%')
AND (Item_17 = 'connected')
AND (Item_18 IN ('10000Mbps', '1000MBps') OR ITEM_9 IS NOT NULL AND ITEM_10 IS NOT NULL)
AND (Item_18 != '100Mbps')
) macaddr ON main.Audit_ID = macaddr.Audit_ID
LEFT JOIN
(
SELECT Audit_ID, Category_ID, Item_1, Record_ordinal
FROM dbo.[Audit_Data] AS t1
WHERE
Item_1 = 'Automatyczna konfiguracja sieci przewodowej' OR Item_1 = 'dot3svc' OR Item_1 = 'Wired AutoConfig'
AND Item_3 = 'Running'
AND Category_ID = '4100'
) t1 ON main.Audit_ID = t1.Audit_ID
WHERE
main.Record_Ordinal = '2'
ORDER BY main.Audit_ID
sql
sql-server tsql database-trigger asked Nov 13 '18 at 11:47
Mikołaj StempniewiczMikołaj Stempniewicz
115
i have a table called Audit_Data, and from time to time there is an update coming. Every single update consists of around 300 rows (around 20 columns per row), and all of the rows from the same update share the same Audit_ID.
I have a select, that pulls out only this data, which is relevant for me. It basically transform the 300x20 field data into one row of data.
Is there a way to create a SQL trigger, that would perform the select on the updated Audit_Data table, and insert selected data into table named Audit_Final?
This is my select statement that i use to pull out the relevant data:
SELECT main.Audit_ID
,main.Item_19
,main.Item_1
,main.Item_7
,main.Item_8
,Item_17
,main.Item_13
,macaddr.Item_2
,macaddr.Item_16
,t1.Item_1
FROM dbo.[Audit_Data] AS main
LEFT JOIN
(
SELECT Audit_ID, Item_2, Item_16
FROM dbo.[Audit_Data] AS macaddr
WHERE
(Item_2 NOT LIKE 'Hyper-V%')
AND (Item_17 = 'connected')
AND (Item_18 IN ('10000Mbps', '1000MBps') OR ITEM_9 IS NOT NULL AND ITEM_10 IS NOT NULL)
AND (Item_18 != '100Mbps')
) macaddr ON main.Audit_ID = macaddr.Audit_ID
LEFT JOIN
(
SELECT Audit_ID, Category_ID, Item_1, Record_ordinal
FROM dbo.[Audit_Data] AS t1
WHERE
Item_1 = 'Automatyczna konfiguracja sieci przewodowej' OR Item_1 = 'dot3svc' OR Item_1 = 'Wired AutoConfig'
AND Item_3 = 'Running'
AND Category_ID = '4100'
) t1 ON main.Audit_ID = t1.Audit_ID
WHERE
main.Record_Ordinal = '2'
ORDER BY main.Audit_ID
sql
sql-server tsql database-trigger sql
sql-server tsql database-trigger asked Nov 13 '18 at 11:47
Mikołaj StempniewiczMikołaj Stempniewicz
115
asked Nov 13 '18 at 11:47
Mikołaj StempniewiczMikołaj Stempniewicz
115
edited Nov 13 '18 at 16:04
Mikołaj Stempniewicz
asked Nov 13 '18 at 11:47
Mikołaj StempniewiczMikołaj Stempniewicz
115
asked Nov 13 '18 at 11:47
Mikołaj StempniewiczMikołaj Stempniewicz
115
asked Nov 13 '18 at 11:47
Mikołaj StempniewiczMikołaj Stempniewicz
115
115
Oh, sorry about that. Updated the initial post with the sql-server tag.
– Mikołaj Stempniewicz
Nov 13 '18 at 12:00
So all you wan is to automate that select statement to happen after every table update (and direct your output into a specific table) is that correct?
– Bartosz X
Nov 13 '18 at 12:03
@BartoszX precisely :)
– Mikołaj Stempniewicz
Nov 13 '18 at 12:15
add a comment |
Oh, sorry about that. Updated the initial post with the sql-server tag.
– Mikołaj Stempniewicz
Nov 13 '18 at 12:00
So all you wan is to automate that select statement to happen after every table update (and direct your output into a specific table) is that correct?
– Bartosz X
Nov 13 '18 at 12:03
@BartoszX precisely :)
– Mikołaj Stempniewicz
Nov 13 '18 at 12:15
Oh, sorry about that. Updated the initial post with the sql-server tag.
– Mikołaj Stempniewicz
Nov 13 '18 at 12:00
Oh, sorry about that. Updated the initial post with the sql-server tag.
– Mikołaj Stempniewicz
Nov 13 '18 at 12:00
So all you wan is to automate that select statement to happen after every table update (and direct your output into a specific table) is that correct?
– Bartosz X
Nov 13 '18 at 12:03
So all you wan is to automate that select statement to happen after every table update (and direct your output into a specific table) is that correct?
– Bartosz X
Nov 13 '18 at 12:03
@BartoszX precisely :)
– Mikołaj Stempniewicz
Nov 13 '18 at 12:15
@BartoszX precisely :)
– Mikołaj Stempniewicz
Nov 13 '18 at 12:15
add a comment |
1 Answer
1
active
oldest
votes
0
Based on authors comment this is what is required here:
CREATE TRIGGER [TR_Audit_Data] ON [Audit_Data]
AFTER UPDATE
AS
BEGIN
INSERT INTO [Audit_Final](cloumn_1, colum_2, ... all columns you have on target table)
/*
Paste your select query here
*/
END
answered Nov 13 '18 at 13:10
Bartosz XBartosz X
1,1071120
unfortunatelly it does not do anything. I mean, the trigger was created, but i cannot see its effects.
– Mikołaj Stempniewicz
Nov 13 '18 at 14:16
So you have created that trigger, done an update on audit_data and it didn't insert anything into audit_final? Yet no errors?
– Bartosz X
Nov 13 '18 at 14:48
In the end i had to use AFTER INSERT instead AFTER UPDATE for the trigger to work as intended, thanks for your help!
– Mikołaj Stempniewicz
Nov 13 '18 at 16:05
You're welcome. If this solved your problem then please accept/up vote my answer.
– Bartosz X
Nov 13 '18 at 17:57
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
});
}
});
draft saved
draft discarded
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%2f53280385%2fsql-trigger-select-into-after-update-insert%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
0
Based on authors comment this is what is required here:
CREATE TRIGGER [TR_Audit_Data] ON [Audit_Data]
AFTER UPDATE
AS
BEGIN
INSERT INTO [Audit_Final](cloumn_1, colum_2, ... all columns you have on target table)
/*
Paste your select query here
*/
END
answered Nov 13 '18 at 13:10
Bartosz XBartosz X
1,1071120
unfortunatelly it does not do anything. I mean, the trigger was created, but i cannot see its effects.
– Mikołaj Stempniewicz
Nov 13 '18 at 14:16
So you have created that trigger, done an update on audit_data and it didn't insert anything into audit_final? Yet no errors?
– Bartosz X
Nov 13 '18 at 14:48
In the end i had to use AFTER INSERT instead AFTER UPDATE for the trigger to work as intended, thanks for your help!
– Mikołaj Stempniewicz
Nov 13 '18 at 16:05
You're welcome. If this solved your problem then please accept/up vote my answer.
– Bartosz X
Nov 13 '18 at 17:57
add a comment |
0
Based on authors comment this is what is required here:
CREATE TRIGGER [TR_Audit_Data] ON [Audit_Data]
AFTER UPDATE
AS
BEGIN
INSERT INTO [Audit_Final](cloumn_1, colum_2, ... all columns you have on target table)
/*
Paste your select query here
*/
END
answered Nov 13 '18 at 13:10
Bartosz XBartosz X
1,1071120
unfortunatelly it does not do anything. I mean, the trigger was created, but i cannot see its effects.
– Mikołaj Stempniewicz
Nov 13 '18 at 14:16
So you have created that trigger, done an update on audit_data and it didn't insert anything into audit_final? Yet no errors?
– Bartosz X
Nov 13 '18 at 14:48
In the end i had to use AFTER INSERT instead AFTER UPDATE for the trigger to work as intended, thanks for your help!
– Mikołaj Stempniewicz
Nov 13 '18 at 16:05
You're welcome. If this solved your problem then please accept/up vote my answer.
– Bartosz X
Nov 13 '18 at 17:57
add a comment |
0
0
0
Based on authors comment this is what is required here:
CREATE TRIGGER [TR_Audit_Data] ON [Audit_Data]
AFTER UPDATE
AS
BEGIN
INSERT INTO [Audit_Final](cloumn_1, colum_2, ... all columns you have on target table)
/*
Paste your select query here
*/
END
answered Nov 13 '18 at 13:10
Bartosz XBartosz X
1,1071120
Based on authors comment this is what is required here:
CREATE TRIGGER [TR_Audit_Data] ON [Audit_Data]
AFTER UPDATE
AS
BEGIN
INSERT INTO [Audit_Final](cloumn_1, colum_2, ... all columns you have on target table)
/*
Paste your select query here
*/
END
answered Nov 13 '18 at 13:10
Bartosz XBartosz X
1,1071120
answered Nov 13 '18 at 13:10
Bartosz XBartosz X
1,1071120
answered Nov 13 '18 at 13:10
Bartosz XBartosz X
1,1071120
answered Nov 13 '18 at 13:10
Bartosz XBartosz X
1,1071120
1,1071120
unfortunatelly it does not do anything. I mean, the trigger was created, but i cannot see its effects.
– Mikołaj Stempniewicz
Nov 13 '18 at 14:16
So you have created that trigger, done an update on audit_data and it didn't insert anything into audit_final? Yet no errors?
– Bartosz X
Nov 13 '18 at 14:48
In the end i had to use AFTER INSERT instead AFTER UPDATE for the trigger to work as intended, thanks for your help!
– Mikołaj Stempniewicz
Nov 13 '18 at 16:05
You're welcome. If this solved your problem then please accept/up vote my answer.
– Bartosz X
Nov 13 '18 at 17:57
add a comment |
unfortunatelly it does not do anything. I mean, the trigger was created, but i cannot see its effects.
– Mikołaj Stempniewicz
Nov 13 '18 at 14:16
So you have created that trigger, done an update on audit_data and it didn't insert anything into audit_final? Yet no errors?
– Bartosz X
Nov 13 '18 at 14:48
In the end i had to use AFTER INSERT instead AFTER UPDATE for the trigger to work as intended, thanks for your help!
– Mikołaj Stempniewicz
Nov 13 '18 at 16:05
You're welcome. If this solved your problem then please accept/up vote my answer.
– Bartosz X
Nov 13 '18 at 17:57
unfortunatelly it does not do anything. I mean, the trigger was created, but i cannot see its effects.
– Mikołaj Stempniewicz
Nov 13 '18 at 14:16
unfortunatelly it does not do anything. I mean, the trigger was created, but i cannot see its effects.
– Mikołaj Stempniewicz
Nov 13 '18 at 14:16
So you have created that trigger, done an update on audit_data and it didn't insert anything into audit_final? Yet no errors?
– Bartosz X
Nov 13 '18 at 14:48
So you have created that trigger, done an update on audit_data and it didn't insert anything into audit_final? Yet no errors?
– Bartosz X
Nov 13 '18 at 14:48
In the end i had to use AFTER INSERT instead AFTER UPDATE for the trigger to work as intended, thanks for your help!
– Mikołaj Stempniewicz
Nov 13 '18 at 16:05
In the end i had to use AFTER INSERT instead AFTER UPDATE for the trigger to work as intended, thanks for your help!
– Mikołaj Stempniewicz
Nov 13 '18 at 16:05
You're welcome. If this solved your problem then please accept/up vote my answer.
– Bartosz X
Nov 13 '18 at 17:57
You're welcome. If this solved your problem then please accept/up vote my answer.
– Bartosz X
Nov 13 '18 at 17:57
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53280385%2fsql-trigger-select-into-after-update-insert%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
Oh, sorry about that. Updated the initial post with the sql-server tag.
– Mikołaj Stempniewicz
Nov 13 '18 at 12:00
So all you wan is to automate that select statement to happen after every table update (and direct your output into a specific table) is that correct?
– Bartosz X
Nov 13 '18 at 12:03
@BartoszX precisely :)
– Mikołaj Stempniewicz
Nov 13 '18 at 12:15