October 26, 2017 at 9:11 am
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
October 26, 2017 at 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!! 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 26, 2017 at 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.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 26, 2017 at 9:38 am
TheSQLGuru - Thursday, October 26, 2017 9:26 AMThere 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
October 26, 2017 at 9:49 am
Steve Jones - SSC Editor - Thursday, October 26, 2017 9:37 AMIt'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 QCThe 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
October 26, 2017 at 11:19 am
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
October 26, 2017 at 3:12 pm
GroverVivek - Thursday, October 26, 2017 9:49 AMYes, 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://qa.sqlservercentral.com/articles/MERGE/73805/
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 26, 2017 at 5:24 pm
GroverVivek - Thursday, October 26, 2017 9:49 AMSteve Jones - SSC Editor - Thursday, October 26, 2017 9:37 AMIt'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 QCThe 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
October 27, 2017 at 6:48 am
Thanks to all for 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