Trigger to insert records on a linked server else in a local server

Question

Trigger to insert records on a linked server else in a local server

Viewing 7 posts - 16 through 21 (of 21 total)

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

R.P.Rozema SSChampion Points: 12317 More actions · Answer 1

kbsk333000 (6/23/2011)
Is there any alternate way in trigger?

Yes there is, it is called SQL Server Service Broker. SSB is SQL server's service/queue mechanisme. It will let you, from the trigger code, create and post a message (which content is for you to fill in) and post it for processing in another thread. SSB's delivery of the message is guaranteed. In such events as password, connection or other availability issues it will store the message in a queue and automatically retry the delivery until it can be successfully delivered at it's destination without interrupting the original end user's transaction.

One word of warning though: SSB has a pretty steep learning curve. It is a great tool to have in your SQL server toolbox, but if you need to deliver fast, go for the other option suggested first and play with SSB in some spare time first.

edit: sorry Jeffrey, I hadn't read past the first page initially.

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

R.P.Rozema SSChampion Points: 12317 More actions · Answer 2

Jeff Moden (12/11/2011)
I've never found a decent use for it before but this sounds like the perfect use for an "INSTEAD OF" trigger against a view. It would guarantee that any INSERT from any source would be handled properly.

Actually, over the years we have used "instead of" triggers on views occasionally and I can tell you that they are a great pain in the *** in practice: Most modeling tools don't realise that dropping a view also 'silently' drops any triggers defined on them. Ergo, every time a change is made to the view and we forget to make the concious decision to use an alter instead of a drop/create on the view, the triggers aren't re-created by the tool. Then when end-users start using the db, nothing seems wrong until they come to the point where they actually insert/update the view. Then an error is raised that is very hard to understand and debug for most people. And these errors keep slipping through our regression tests...

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

richardkel SSC Enthusiast Points: 137 More actions · Answer 3

ALZDBA (12/11/2011)
Whenever someone holds a blocking lock on your linked server table, your source server 's operations will come to a halt because your insert transactions will fail !
As others suggested, there are other ways of doing this kind of things.
In my experience, most "must be immediate" requirements aren't that immediately needed at all.
Re-assess this requirement and sell a service broker based solution.
It has the big advantage both involved instances can still be taken offline without immediate downtime of the partner instances application(s)

Everyone seems to be saying don't use triggers, but how do you get the record keys into the staging table for the records that need to be transferred by this scheduled job or other like process? Do you sift through the log files and find the records that you care about that have been updated in a way that you care about? I get not having the trigger talk through the linked server, and using an scheduled process for this, but somehow you have to know what records to push through the link. They won't magically show up in a staging table just because I create it, something has to put them there.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 4

richardkel (12/12/2011)
...
Everyone seems to be saying don't use triggers, but how do you get the record keys into the staging table for the records that need to be transferred by this scheduled job or other like process?

Now, that's one example where you can use a trigger.

Just to record the key values into a "toBeProcessed" table.

That can be fed using a trigger because it is local, short and doesn't need extra data to be fetched at insert time, hence no dependencies of anything other than what is already in the transaction.

Do you sift through the log files and find the records that you care about that have been updated in a way that you care about? I get not having the trigger talk through the linked server, and using an scheduled process for this, but somehow you have to know what records to push through the link. They won't magically show up in a staging table just because I create it, something has to put them there.

You've just described how one should do it if SSB isn't in use.

Have the trigger insert into a toBeProcessed table and have a sqlagent job ( scheduled or launched using an alert ) process the data pump ( push or pull ).

Many of us, me included, still prefer this way of doing things. (because of the issues with troubleshooting SSB)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 5

richardkel (12/12/2011)
...
Everyone seems to be saying don't use triggers, but how do you get the record keys into the staging table for the records that need to be transferred by this scheduled job or other like process? Do you sift through the log files and find the records that you care about that have been updated in a way that you care about? I get not having the trigger talk through the linked server, and using an scheduled process for this, but somehow you have to know what records to push through the link. They won't magically show up in a staging table just because I create it, something has to put them there.

Exactly but you do need trigger for it if and only if DMLs are not coming through Stored Procedures (i.e. ad hoc DMLs). IF you are performing DMLs through Stored Procedures, add one INSERT statement for ToBeProcessed table as a last step.

As a side note if your DMLs are not coming from Stored Procedures / SSIS packages then your environment is not controlled. You should consider it seriously.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 6

ALZDBA (12/11/2011)
[sarcasm]
As a firefighter sysadmin/dba, I cannot but embrace your choice of solution.
( insert in table a server a with a trigger to insert into serverb some table)
This will guarantee my job !
[/sarcasm]
Whenever someone holds a blocking lock on your linked server table, your source server 's operations will come to a halt because your insert transactions will fail !
As others suggested, there are other ways of doing this kind of things.
In my experience, most "must be immediate" requirements aren't that immediately needed at all.
Re-assess this requirement and sell a service broker based solution.
It has the big advantage both involved instances can still be taken offline without immediate downtime of the partner instances application(s)

Heh... no need for the sarcasm, Johan. 😉 Your point is well made.

Personally, I don't like the idea of even using SB even though I imagine it would work very well. It seems like a bit of a system redesign to keep from having to put data on two different servers would be the way to go.

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

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 7

Jeff Moden (12/12/2011)
ALZDBA (12/11/2011)
[sarcasm]
As a firefighter sysadmin/dba, I cannot but embrace your choice of solution.
( insert in table a server a with a trigger to insert into serverb some table)
This will guarantee my job !
[/sarcasm]
Whenever someone holds a blocking lock on your linked server table, your source server 's operations will come to a halt because your insert transactions will fail !
As others suggested, there are other ways of doing this kind of things.
In my experience, most "must be immediate" requirements aren't that immediately needed at all.
Re-assess this requirement and sell a service broker based solution.
It has the big advantage both involved instances can still be taken offline without immediate downtime of the partner instances application(s)
Heh... no need for the sarcasm, Johan. 😉 Your point is well made.
Personally, I don't like the idea of even using SB even though I imagine it would work very well. It seems like a bit of a system redesign to keep from having to put data on two different servers would be the way to go.

This old saying is still true:

"You can lead a horse to water, but you can't make it drink"

Another one:

"Make it foolproof and they'll come up with a better fool"