Adding multiple triggers to same table - Recommendations/Best Practices

Question

Adding multiple triggers to same table - Recommendations/Best Practices

Viewing 8 posts - 31 through 37 (of 37 total)

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

SQL!$@w$0ME SSChampion Points: 12349 More actions · Answer 1

Copy all changes to the table, not few columns. Thanks

Michael L John One Orange Chip Points: 27038 More actions · Answer 2

Without knowing the details of the requirements, it does sound over-complicated. I guess the biggest question is what is the need (or requirement) to make a completely separate copy of the data? Why can't the ETL just grab the new or changed records, and populate the data warehouse?
Assuming that it's a busy system, the overhead of the triggers to copy changed data may significant. Even with Jeff's efficient triggers!
Replication also seems like over-kill.

Here's a few ideas that you may be able to leverage:
1. In the trigger, if an insert or update occurs, write the primary key, and an indicator of insert or updated for each affected table. A second process, we did it with T-SQL, on a schedule would then query these tables, and create or update the records on the second system.
2. Set up an availability group with a read only secondary. If you set up read-only routing in your connection string, you can query for changes against the secondary. Licensing costs may be an issue here, but you gain HA and redundancy.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

SQL!$@w$0ME SSChampion Points: 12349 More actions · Answer 3

500 tables I mentioned is 1/4th of the count of the whole tables on that dB. Initial load can be done as one time load and incremental load for changed/new data can be done daily with ssis etl. Thanks!

Michael L John One Orange Chip Points: 27038 More actions · Answer 4

SQL!$@w$0ME - Tuesday, February 20, 2018 7:55 AM
500 tables I mentioned is 1/4th of the count of the whole tables on that dB. Initial load can be done as one time load and incremental load for changed/new data can be done daily with ssis etl. Thanks!

Still does not answer the question. What is the justification for essentially copying 500 tables to a second location?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

RonKyle SSC-Dedicated Points: 31537 More actions · Answer 5

Still does not answer the question. What is the justification for essentially copying 500 tables to a second location?

We have found a replicated version necessary for intra day downloads. The data warehouse extract has sufficient pull on the database as to interfere with the users if pulled during the day from the primary server. Pulling from a replicated version, which is a complete copy, is a significant advantage. It also acts as a go to database in the event of an issue with the primary database.

SQL!$@w$0ME SSChampion Points: 12349 More actions · Answer 6

This is a business requirement to keep this data in warehouse dB. I’m not aware of anything behind the scenes.

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

SQL!$@w$0ME - Tuesday, February 20, 2018 8:45 AM
This is a business requirement to keep this data in warehouse dB. Iâ€™m not aware of anything behind the scenes.

Not exactly the explanation we were looking for. We wanted to know the reason for the business requirement.

Anyhow, based on what you said, you want to keep entire rows that have changed for every one of the 500+ tables? (Just making sure). If that's true, then you just might want to use replication. If you need it to be real time, you should use transactional replication. While triggers are ok for such a thing, they may not tell you when someone adds a column, they do take a bit of extra time to execute, and they won't handle blobs in a straight forward manner.

--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)

Michael L John One Orange Chip Points: 27038 More actions · Answer 8

RonKyle - Tuesday, February 20, 2018 8:32 AM
Still does not answer the question. What is the justification for essentially copying 500 tables to a second location?
We have found a replicated version necessary for intra day downloads. The data warehouse extract has sufficient pull on the database as to interfere with the users if pulled during the day from the primary server. Pulling from a replicated version, which is a complete copy, is a significant advantage. It also acts as a go to database in the event of an issue with the primary database.

Valid explanation. I'm actually trying to find out why the OP determined that a similar, and vastly more complicated, approach was required for their environment.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/