Trigger on linked tables

[kaiuweb]

Hi,
I am total beginner but have a question.
Is it possible to create triggers on linked tables in SQL Server.

 

[tlbroadbent]

Explain what you mean by linked tables. Are you referring to SQL Server tables linked to an Access database or tables in a linked server database? Or do you mean something completely different? If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[DBADoug]

My thoughts are that you would apply the trigger on the linked table at the source. In other words, if you are working on SERVERA linked to a SQL Server SERVERB that has a table that you are using via the link, then when you insert records into this table through your distributed query, the trigger on the remote server would fire, assuming that you have an insert trigger on it.

 

[topher0303]

According to BOL, triggers can only reside in the same database as the table. However, code inside the trigger can reference objects outside the database.

Chris.

 

[kaiuweb]

Thanks for your replies.

What I had in mind was , linking an Oracle table TABLE-X, from SERVER-A to a SQLSERVER-B.
When new records are added to TABLE-X on SERVER-A with certain values, then I want SQLSERVER-B to perform a few tasks eg copying the record from TABLE-X to a table within SQLSERVER-B.
Not permitted to work directly on SERVER-A only view data for reports etc, hence my question.
Thanks anyway.

 

[tlbroadbent]

A Trigger must be created for the table that is being updated. In your scenario, you want to perform some actions when the Oracle table on sServer_A is updated. Therefore, the Trigger must be created on the Oracle table.

You could create a procedure on Server-B to check the Server-B table for updates and take action on Server-B. This procedure could be scheduled to run periodically. The success of this type of procedure dedpends on the abilty to determine if the Oracle table has been updated since the last time the job ran. If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[kaiuweb]

Thanks Terry, much appreciated.