Should I use Insert Trigger or not?

Question

Should I use Insert Trigger or not?

GroverVivek

SSCommitted

Points: 1674
More actions
October 26, 2017 at 9:11 am

#357986

Hello,
Development team has got following requirement:
1. We have a table 'Tab' which is an existing table where data is inserted from multiple applications. Average 3000 new rows in a day these days.
2. We have a table 'QC' with some configuration values. Those configuration are for rows existing in 'Tab1' and can be matched with columns Col1 and Col2 of both table.
3. Now, client is saying that we want save data in a table where we can see what was its configuration of each row in Tab table when it was inserted.
So, our suggestion was that you can modify you application code by adding insert statements. But, development team is saying that there are lot of applications which are randomly and untimely inserting data from file (whenever file is avaible) in table 'Tab' and some of those applications are not achievable for us. So, they want me to create an Insert trigger on table 'Tab'.I have seen through google that we should avoid trigger and there are performance disadvantages. But I am not able to take final decesion that should I go for trigger and it will not be an headache in future. Please give your suggestions.
Regards
VG

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

There is no concern whatsoever for insert trigger performance for your situation, as long as you aren't doing table scans on massive tables do to the joins or get the data you need. 3000 rows a day can be processed on an abacus, so SQL Server won't even notice!! 😀

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

It's not completely clear what you mean, but I'll guess.

First, I think you have a transactional table, Tab, where you insert rows, but want to know the values of QC at the time of any insert into Tab. Is that right?

If so, what I think is that you really want to track two things
- when a row in Tab changes
- the config at different times in QC

The first is adding a column in Tab that captures the current sysdatetime on insert. (if you need update, use a second column)
The second is adding from/to dates, as in a Slowly Changing Dimension on QC. Use a trigger on QC to capture config changes on dates. Then you can query for the "view" of QC at any point in time. If you are on SQL 2016, this is easy with a Temporal table.

GroverVivek SSCommitted Points: 1674 More actions · Answer 3

TheSQLGuru - Thursday, October 26, 2017 9:26 AM
There is no concern whatsoever for insert trigger performance for your situation, as long as you aren't doing table scans on massive tables do to the joins or get the data you need. 3000 rows a day can be processed on an abacus, so SQL Server won't even notice!! 😀

I am happy to see feedback 😀

Regards
VG

GroverVivek SSCommitted Points: 1674 More actions · Answer 4

Steve Jones - SSC Editor - Thursday, October 26, 2017 9:37 AM
It's not completely clear what you mean, but I'll guess.
First, I think you have a transactional table, Tab, where you insert rows, but want to know the values of QC at the time of any insert into Tab. Is that right?
If so, what I think is that you really want to track two things
- when a row in Tab changes
- the config at different times in QC
The first is adding a column in Tab that captures the current sysdatetime on insert. (if you need update, use a second column)
The second is adding from/to dates, as in a Slowly Changing Dimension on QC. Use a trigger on QC to capture config changes on dates. Then you can query for the "view" of QC at any point in time. If you are on SQL 2016, this is easy with a Temporal table.

Yes, you guessed right way.
One question- Whenever there is any change in configuration, then what will be FromDate? Will it be getdate() default. And what will be ToDate? It must be entered, a NULL?

Regards
VG

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

My recommendation is that you be really careful in your trigger design. As Kevin pointed out, the trigger won't be a performance problem if you write good code and that's not difficult. But, there are other considerations because the new table is what most call an "Audit Table.

The first consideration is that your trigger MUST be able to handle more than one row during a single insert statement execution. The second consideration is the concern of unnecessarily duplicating on insert. The audit trigger should only save data to the audit table for UPDATEs and DELETEs and NOT inserts because if a row never changes, you've still duplicated the data. If a row never changes, the original entry will be in the original table. If an original row get's updated or deleted, then save from the DELETED logical table in the trigger. The original row will be moved copied to the audit table and the latest row will be in the original table.

That's the bottom line here... the latest row for a given "ID" will always be in the original table whether is has ever been modified or not, for inserts and updates. All changes to rows leading up to and not including the latest row will be found in the audit table. Of course, DELETEs will only be found in the audit table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 6

GroverVivek - Thursday, October 26, 2017 9:49 AM
Yes, you guessed right way.
One question- Whenever there is any change in configuration, then what will be FromDate? Will it be getdate() default. And what will be ToDate? It must be entered, a NULL?

This might help you. You can design either way, but you have to be consistent: http://www.sqlservercentral.com/articles/MERGE/73805/

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

GroverVivek - Thursday, October 26, 2017 9:49 AM
Steve Jones - SSC Editor - Thursday, October 26, 2017 9:37 AM
It's not completely clear what you mean, but I'll guess.
First, I think you have a transactional table, Tab, where you insert rows, but want to know the values of QC at the time of any insert into Tab. Is that right?
If so, what I think is that you really want to track two things
- when a row in Tab changes
- the config at different times in QC
The first is adding a column in Tab that captures the current sysdatetime on insert. (if you need update, use a second column)
The second is adding from/to dates, as in a Slowly Changing Dimension on QC. Use a trigger on QC to capture config changes on dates. Then you can query for the "view" of QC at any point in time. If you are on SQL 2016, this is easy with a Temporal table.
Yes, you guessed right way.
One question- Whenever there is any change in configuration, then what will be FromDate? Will it be getdate() default. And what will be ToDate? It must be entered, a NULL?

I've found that using NULLs in a "ToDate" column is wickedly counter productive when it comes to criteria in WHERE clauses because you must check for both a date/time and a null. Many will disagree but I use the default date for "ToDate" columns of "9999", which translates to 9999-01-01. That also leaves a bit of headroom for calculations that require things in the general format of FromDate <= SomeDate < ToDate+1.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GroverVivek SSCommitted Points: 1674 More actions · Answer 8

GroverVivek

SSCommitted

Points: 1674

October 27, 2017 at 6:48 am

#1965496

Thanks to all for suggestions.

Regards
VG