捕获mssqlservice 修改表后的数据，统一存储到特定的表中，之后通过代码同步两个库的数据

CREATE TRIGGER [dbo].[itrg_triggerTest]  ON [dbo].[Mytest]
 after  insert , update ,delete
AS
set nocount on 
--查询该触发器触发的表
--SELECT @@PROCID, OBJECT_NAME( @@PROCID ) 
 declare  @tableName nvarchar(25)  ;

--宣告变量 
DECLARE @D BIT = 0 
DECLARE @I BIT = 0 

declare @content nvarchar(max)  ;
declare @actiontype int  ;
declare @actionName nvarchar(15)  ;
IF EXISTS(SELECT TOP 1 1 FROM DELETED) 
begin 
SET @D = 1 
end 

IF EXISTS(SELECT TOP 1 1 FROM INSERTED) 
begin
SET @I = 1 
end 


IF @I = 1 AND @D = 0 
begin 
 set @actiontype=1  ;
  set @actionName=‘Insert‘
--select N‘插入‘+‘insert into ‘
 select  @content= (select   * from inserted for xml path)+‘‘
  
 -- select   * from inserted for xml path(@tableName),root(‘insert‘)
end 
 else  IF @I = 1 AND @D = 1 
begin 
 set @actiontype=2  ;
  set @actionName=‘Update‘ ;
 select  @content= (select   * from inserted for xml path)+‘‘ ;
 
end 
else 
begin 

 set @actiontype=3  ;
  set @actionName=‘Delete‘
--select N‘删除‘+‘insert into ‘
 select  @content= (select   * from deleted for xml path)+‘‘ ;
end 


if ( @content is not null )
begin 

SELECT  @tableName =OBJECT_SCHEMA_NAME( parent_id ) + ‘.‘ + OBJECT_NAME( parent_id )  FROM sys.triggers WHERE object_id = @@PROCID

insert into  testdatabaseLog   
  (
  ChangeTableName ,
  createdatetime ,  
   ActionType , 
   ActionName  , 
   content,FromStore)
   values  ( @tableName ,GETDATE() , @actiontype, @actionName  ,@content,   1)  ;
end 


GO

-- 测试数据
  insert into Mytest values (1,‘苹果‘,1)
  insert into Mytest values (2,‘雪梨‘,1)
  insert into Mytest values (3,‘香蕉‘,1)

得到结果 测试

捕获mssqlservice 修改表后的数据，统一存储到特定的表中，之后通过代码同步两个库的数据

标签：ice   time   trigger   ase   into   let   service   --   过程