When a new data is inserted into the local database (sql server), it is synchronized to the server database

One of my classmates asked me how to synchronize the local database to the server when inserting new data. At that time, I first thought about the program logic control and corresponding processing.

But sometimes our program is not easy to deal with. Can we start from the database? Isn't there a trigger in the database? It should be OK. Let's do it with this. I don't know much about some things. Thanks for the information I found on the Internet. If there is any misunderstanding, please correct it!

1. First, understand what triggers are

trigger is a method provided by SQL server to programmers and data analysts to ensure data integrity. It is a special stored procedure related to table events. Its execution is not called by programs, nor started manually, but triggered by events. When a table is operated (insert, delete, update), it will activate its execution. Triggers are often used to enhance data integrity constraints and business rules. Triggers can be found in DBA triggers, user triggers data dictionary.

Let's briefly talk about the syntax for creating a trigger:

CREATE TRIGGER trigger_name
 ON table_name
 [WITH ENCRYPTION]
  FOR | AFTER | INSTEAD OF [DELETE, INSERT, UPDATE]
 AS 
  T-SQL Sentence
GO

2. How to connect to the server database

select * from sys.servers --View current services
EXEC sp_addlinkedserver
@server='black',--Alias of the server being accessed (it is customary to use the target server directly IP,Or take an alias such as: black)
@srvproduct='',
@provider='SQLOLEDB',
@datasrc='192.168.1.36', --Servers to access
@catalog='anmax' --Database name
exec sp_addlinkedsrvlogin 'black','false',null,'sa','sa123456' --(balck->The alias taken above; sa->Database user name; sa123456->Password)
select top 10 * from [black].[anmax].dbo.ad --Test success
exec sp_droplinkedsrvlogin 'black',null --Remove login user
exec sp_dropserver 'black' --Remove service

3. Synchronously inserted data

create trigger trgtest
on product
after insert
as 
begin
--select *  from sys.servers --View current services
EXEC sp_addlinkedserver
@server='black',--Alias of the server being accessed (it is customary to use the target server directly IP,Or take an alias such as: JOY)
@srvproduct='',
@provider='SQLOLEDB',
@datasrc='192.168.1.36', --Servers to access
@catalog='anmax' --Database name
exec sp_addlinkedsrvlogin 'black','false',null,'sa','sa123456'
--Execute synchronization code
declare @proId int;
set @proId=@@identity;--Inserted data ID
select * into [Your server table] from product where productid=@Proid

exec sp_droplinkedsrvlogin 'black',null
exec sp_dropserver 'black'

end

Some problems encountered during this period: enable TCP/IP protocol

 

Tags: SQL Server Database SQL Stored Procedure

Posted on Sat, 04 Apr 2020 04:39:41 -0700 by Katanius