Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2003
    Posts
    176

    Unanswered: Triggers on linked servers

    Can i write triggers beteen the linked servers.Here is the whole scenario. I have 3 servers. Server aaaa and server bbbb has replication in between them.Server aaaa is a publisher and server bbbb is the suscriber. I have another server cccc.So If i make any change on a table xxxx should effect the tablee xxxx on server aaaa and bbbb.So i am writing a trigger(for insert,update and delete on the table) xxxx on the server ccccc.So that trigger should take care of any DML(insert,update and delete) happend on table xxxx on server cccc and should effect on server aaaa and then the replication should take care of server bbbb.This want i am planning right now?Is it a good practice to implement in such a requirement?
    Please help me.
    Thanks.

  2. #2
    Join Date
    Apr 2003
    Posts
    176
    Can anyone help me in this...

    Thanks once again.

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    You could use triggers with DTC(distributed transaction coordinator) but you have to remember that if any of servers is not accessible - you cannot update any data in table with this trigger.

  4. #4
    Join Date
    Apr 2003
    Posts
    176
    Thanks for ur information.How can i set dtc on on my server.Should i set DTC on both the servers(source and target server?)

  5. #5
    Join Date
    Apr 2003
    Posts
    176
    Thanks for ur information.How can i set dtc on on my server.Should i set DTC on both the servers(source and target server?)

  6. #6
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by bruce_Reid
    Thanks for ur information.How can i set dtc on on my server.Should i set DTC on both the servers(source and target server?)
    DTC must be started on every server involved in DISTRIBUTED TRANSACTIONs.

    BOL:

    For example, if BEGIN DISTRIBUTED TRANSACTION is issued on ServerA, the connection calls a stored procedure on ServerB and another stored procedure on ServerC, and the stored procedure on ServerC executes a distributed query against ServerD, then all four SQL servers are involved in the distributed transaction. ServerA is the originating, controlling server for the transaction.

    The connections involved in Transact-SQL distributed transactions do not get a transaction object they can pass to another connection for it to explicitly enlist in the distributed transaction. The only way for a remote server to enlist in the transaction is to be the target of a remote stored procedure call or a distributed query.

    The sp_configure remote proc trans option controls whether calls to remote stored procedures in a local transaction automatically cause the local transaction to be promoted to a distributed transaction managed by MS DTC. The connection-level SET option REMOTE_PROC_TRANSACTIONS can be used to override the server default established by sp_configure remote proc trans. With this option set on, a remote stored procedure call causes a local transaction to be promoted to a distributed transaction. The connection that creates the MS DTC transaction becomes the originator for the transaction. COMMIT TRANSACTION initiates an MS DTC coordinated commit. If the sp_configure remote proc trans option is set on, remote stored procedure calls in local transactions are automatically protected as part of distributed transactions without having to rewrite applications to specifically issue BEGIN DISTRIBUTED TRANSACTION instead of BEGIN TRANSACTION.

    When a distributed query is executed in a local transaction, the transaction is automatically promoted to a distributed transaction if the target OLE DB data source supports ITransactionLocal. If the target OLE DB data source does not support ITransactionLocal, only read-only operations are allowed in the distributed query.

    For more information about the distributed transaction environment and process, see the Microsoft Distributed Transaction Coordinator documentation.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •