SQL - trigger select into after update/insert

Question

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

Oh, sorry about that. Updated the initial post with the sql-server tag. — Mikołaj Stempniewicz
– Mikołaj Stempniewicz, Commented Nov 13, 2018 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
– Bartosz X, Commented Nov 13, 2018 at 12:03

Bartosz X · Accepted Answer · 2018-11-13 13:10:15Z

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, 2018 at 13:10

Bartosz X

2,86828 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mikołaj Stempniewicz Over a year ago

unfortunatelly it does not do anything. I mean, the trigger was created, but i cannot see its effects.

Bartosz X Over a year ago

So you have created that trigger, done an update on audit_data and it didn't insert anything into audit_final? Yet no errors?

Mikołaj Stempniewicz Over a year ago

In the end i had to use AFTER INSERT instead AFTER UPDATE for the trigger to work as intended, thanks for your help!

Bartosz X Over a year ago

You're welcome. If this solved your problem then please accept/up vote my answer.

Collectives™ on Stack Overflow

SQL - trigger select into after update/insert

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related