SQL Server Table Trigger over two Database Server with the same Table Stucture

%3CLINGO-SUB%20id%3D%22lingo-sub-332944%22%20slang%3D%22en-US%22%3ESQL%20Server%20Table%20Trigger%20over%20two%20Database%20Server%20with%20the%20same%20Table%20Stucture%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-332944%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20I%20have%20a%20question%3CBR%20%2F%3EIs%20it%20posible%20to%20create%20a%20Trigger%20after%20Update%20to%20write%20the%20hole%20updated%20data%20to%20an%20identical%20sql%20server%20with%20the%20same%20table%20structur%3F%3C%2FP%3E%3CP%3ETo%20describe%20this%20in%20more%20detail%3A%20Two%20database%20servers%20with%20identical%20tables.%20One%20Process%20writes%20data%20into%20Table%20'A'%20and%20now%20I%20want%20that%20a%20trigger%20(after%20insert%20or%20after%20update)%20writes%20the%20same%20Data%20into%20table%20'A'%20on%20the%20second%20database%20server%2C%20with%20the%20identical%20table%20with%20the%20same%20Structure%20and%20the%20same%20Login.%20Is%20this%20possible%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreetings%20from%20Germany%3C%2FP%3E%3CP%3EMichael%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EGreetings%20from%20germany%3CBR%20%2F%3EMichael%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-651915%22%20slang%3D%22en-US%22%3ERe%3A%20SQL%20Server%20Table%20Trigger%20over%20two%20Database%20Server%20with%20the%20same%20Table%20Stucture%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-651915%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280595%22%20target%3D%22_blank%22%3E%40MichaelBurkhardt%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes.%20Please%20follow%20below%20step%3A%3C%2FP%3E%3COL%3E%3CLI%3ECreate%20Linked%20server%20and%20set%26nbsp%3B%3CSPAN%3E%22RPC%22%20and%20%22RPC%20Out%22%20to%20%22True%22%20from%20%22Security%20Option%22.%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3ELinked%20server%20user%20have%20insert%20access%20on%20table.%3C%2FLI%3E%3CLI%3EWrite%20four%20part%20naming%20while%20inserting%20data%20in%20trigger%3C%2FLI%3E%3C%2FOL%3E%3CPRE%3EINSERT%20INTO%20%5BLINKED-SERVER-IP%5D.MyDb.dbo.Customer%20(CustomerId%2C%20CustomerName)%0ASELECT%20CustomerId%2C%20CustomerName%20FROM%20MyCustomers%20WHERE%20CustomerId%20%3D%205%3C%2FPRE%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%204.%20Enable%26nbsp%3B%3CSPAN%3EDistributed%20transactions%20on%20server.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fdba.stackexchange.com%2Fquestions%2F218936%2Finsert-trigger-to-a-linked-server-error%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdba.stackexchange.com%2Fquestions%2F218936%2Finsert-trigger-to-a-linked-server-error%3C%2FA%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Hello I have a question
Is it posible to create a Trigger after Update to write the hole updated data to an identical sql server with the same table structur?

To describe this in more detail: Two database servers with identical tables. One Process writes data into Table 'A' and now I want that a trigger (after insert or after update) writes the same Data into table 'A' on the second database server, with the identical table with the same Structure and the same Login. Is this possible?

 

Greetings from Germany

Michael


Greetings from germany
Michael

1 Reply
Highlighted

@MichaelBurkhardt 

Yes. Please follow below step:

  1. Create Linked server and set "RPC" and "RPC Out" to "True" from "Security Option".
  2. Linked server user have insert access on table.
  3. Write four part naming while inserting data in trigger
INSERT INTO [LINKED-SERVER-IP].MyDb.dbo.Customer (CustomerId, CustomerName)
SELECT CustomerId, CustomerName FROM MyCustomers WHERE CustomerId = 5

    4. Enable Distributed transactions on server. 

https://dba.stackexchange.com/questions/218936/insert-trigger-to-a-linked-server-error