sql-server – Periodically Update Table with New Records from Another Server

Mostly Copy/Pasting my response from this question on stackoverflow

Triggers can be very alluring, when you first start using them they seem like a magic bullet to all kinds of problems. But, they make "magic" stuff happen, if you don't know the database inside out, it can seem like really strange things happen (such as inserts into other tables, input data changing, etc). Before implementing things as a trigger I'd seriously consider instead enforcing the use of an API around the schema (preferably in the database, but outside if you can't).

Some things I'd still use triggers for

• Keeping track of "date_created" and "date_last_edited" fields
• Inserting "ID"'s (in oracle, where there is no auto id field)
• Keeping change history

Things you wouldn't want to use triggers for

• business rules/logic
• anything which connects outside of the database (eg a webservice call)
• Access control
• Anything which isn't transactional ( anything you do in the trigger MUST be able to rollback with the transaction )

There aren't many issues beyond what you have now that I can think of off-hand (I'm sure someone will correct me though!). Since you are proposing to have your audit DB on the same server, and, I presume, instance, then by using three part naming you are all set. You've already noted the unlikely possibility of someone removing the audit DB and likewise there's a slim chance someone might re-name it, move it, leave it in an uncertain state by some failed maintenance and so on, but again these aren't that likely. If all your DBs are in the same file group or all on oen disk/array of disks then there might be a performance implication, depending on the level of traffic you expect so you might want to consider where your audit DB physically resides quite carefully: Same SQL instance doesn't have to mean the same physical disk.

With regards to trigger design, you would most likely be using AFTER INSERT and you could handle exceptions inside the trigger so that they are not raised to the caller and therefore the transaction completes on the parent table. Of course, you wouldn't be getting audit data then and you must decide if that is a problem for you and if so you'll need a suitable strategy for raising an alert of some kind. But you'll need to consider if it is right and acceptable to swallow exceptions in that way: Most developers I know (and I am one!) would look like you'd asked them to do something un-natural with a chicken if you asked them to bury exceptions in that way.

I'm not sure I understand the rationale for a job which copies and deletes records: That's a programming smell to me. Why over-complicate things? You're right to question it.