Question

如何利用触发器实现两个数据库间的同步

Answer 1

SQL Server 2005的联机帮助： Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger) CREATE TRIGGER [ schema_name . ]trigger_name ON { table | view } [ WITH [ ,...n ] ] { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } [ WITH APPEND ] [ NOT FOR REPLICATION ] AS { sql_statement [ ; ] [ ...n ] | EXTERNAL NAME } ::= [ ENCRYPTION ] [ EXECUTE AS Clause ] ::= assembly_name.class_name.method_name 以下是一个例子 set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go -- ============================================= -- Author: gsoosg -- Create date: 2007-12-24 -- Description: -- ============================================= CREATE TRIGGER [Trigger_Add_Carduser] ON [dbo].[carduser] AFTER INSERT AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; set insert ParkFee.dbo.card_user(card_user_id,card_user_name,sex) select card_user_id,card_user_name,sex from inserted END 说明： 上例实现了为当前数据库中表carduser创建触发器当插入数据时，同步向ParkFee数据库表dbo.card_user插入数据，从而达到同步插入。类同，可将insert语句改为update，delete。 注意: 若想利用此方法达到反向同步则可能出现问题，比如想在parkfee数据库有新数据插入时让当前数据库也同时插入一条记录，建立一个触发器，则形成了一个循环触发，当插入数据时会报大于最大递归次数错。因此因避免这样的触发循环，若要达到类似效果还须想别的方法。