写触发器+代码。 写几个触发器(insert,update等)向一张表插入新增的、修改的或删除的数据 你写个小程序访问这张表,把数据取走(你得设置个),物理删除或逻辑删除你已经取走的数据(肯定要有事务)以下是一个insert触发器实例: 当消费表有新数据插入时,便向短信表插入该条记录GOIF EXISTS( SELECT name FROM sysobjects WHERE name='TrgChargeInsert' AND type='TR') DROP TRIGGER TrgChargeInsert GO RAISERROR ('CREATE TRIGGER: TrgChargeInsert',0,1) WITH NOWAIT GO CREATE TRIGGER TrgChargeInsert ON CardDealRecordConsume FOR INSERT AS IF @@rowcount = 0 RETURN
SET NOCOUNT ON
DECLARE @CustomerID INT, @MobilePhone VARCHAR(20), @DealAmount Money, @ArisesTime DATETIME, @BookID INT,@PhysicalCode CHAR(10),@CardID INT,@SendMessage VARCHAR(255) IF EXISTS(SELECT * FROM Inserted where BookID in(101,102,103,104,105))
BEGIN SELECT @CustomerID=CustomerID,@DealAmount=DealAmount,@ArisesTime=ArisesTime,@PhysicalCode=PhysicalCode,@CardID=CardID FROM Inserted where BookID in(101,102,103,104,105) SELECT @MobilePhone = MobilePhone FROM VwCustomer WHERE PhysicalCode = @PhysicalCode IF @MobilePhone IS NOT NULL BEGIN SET @SendMessage = '您好!您的校园卡于 ' + DATENAME(YEAR, @ArisesTime) + '年' + DATENAME(MONTH, @ArisesTime) + '月' + DATENAME(DAY, @ArisesTime) + '日 ' + DATENAME(HOUR, @ArisesTime) + '时' + DATENAME(MINUTE, @ArisesTime) + '分' + ' 充值了 ' + CAST(@DealAmount AS VARCHAR(8)) + ' 元'
INSERT INTO SmsMessage(ArisesTime, PhysicalCode, CardID, CustomerID, MobilePhone, SendMessage) VALUES(@ArisesTime, @PhysicalCode, @CardID, @CustomerID, @MobilePhone, @SendMessage) END END
如果两个数据库的表结构不一样可以同步吗,比如服务器A上的数据库A的TableA要同步到服务器B上的数据库B的两个表,一个表同步TableA的表名,一个表同步TableA相对应的表内容,这样可以吗???
搞一个类似于LastSyncTime的变量,记录下上一次同步的时间
定时的去读取A库记录,select * from Table where TM > LastSyncTime的记录,如果有,然后再依据规则写入B库,同时更新LastSymcTime的值
如果数据更新很快,可以适当的减少定时器的时间,但总体上A库和B库会存在一定的时间差当然也可以通过数据库,采用解发器或是事务来同步,但没有写代码方便如果是远程同步,取出数据出来,序列化,然后socket到远端,接收程序收到后,反序列化,根据规铡再写入到B库
必须是控制台程序或是winform程序因为WEB程序需要访问才能执行
写几个触发器(insert,update等)向一张表插入新增的、修改的或删除的数据
你写个小程序访问这张表,把数据取走(你得设置个),物理删除或逻辑删除你已经取走的数据(肯定要有事务)以下是一个insert触发器实例:
当消费表有新数据插入时,便向短信表插入该条记录GOIF EXISTS( SELECT name FROM sysobjects WHERE name='TrgChargeInsert' AND type='TR')
DROP TRIGGER TrgChargeInsert
GO
RAISERROR ('CREATE TRIGGER: TrgChargeInsert',0,1) WITH NOWAIT
GO
CREATE TRIGGER TrgChargeInsert ON CardDealRecordConsume FOR INSERT
AS
IF @@rowcount = 0
RETURN
SET NOCOUNT ON
DECLARE @CustomerID INT, @MobilePhone VARCHAR(20), @DealAmount Money, @ArisesTime DATETIME, @BookID INT,@PhysicalCode CHAR(10),@CardID INT,@SendMessage VARCHAR(255) IF EXISTS(SELECT * FROM Inserted where BookID in(101,102,103,104,105))
BEGIN
SELECT @CustomerID=CustomerID,@DealAmount=DealAmount,@ArisesTime=ArisesTime,@PhysicalCode=PhysicalCode,@CardID=CardID FROM Inserted where BookID in(101,102,103,104,105)
SELECT @MobilePhone = MobilePhone
FROM VwCustomer WHERE PhysicalCode = @PhysicalCode
IF @MobilePhone IS NOT NULL
BEGIN
SET @SendMessage = '您好!您的校园卡于 '
+ DATENAME(YEAR, @ArisesTime) + '年' + DATENAME(MONTH, @ArisesTime) + '月' + DATENAME(DAY, @ArisesTime) + '日 ' + DATENAME(HOUR, @ArisesTime) + '时' + DATENAME(MINUTE, @ArisesTime) + '分' + ' 充值了 ' + CAST(@DealAmount AS VARCHAR(8)) + ' 元'
INSERT INTO SmsMessage(ArisesTime, PhysicalCode, CardID, CustomerID, MobilePhone, SendMessage)
VALUES(@ArisesTime, @PhysicalCode, @CardID, @CustomerID, @MobilePhone, @SendMessage)
END END
下面是带有事务的操作sql函数 /// <summary>
/// 带事务的批量操作数据。
/// </summary>
/// <param name="sqlInsert">SQL</param>
/// <returns>操作是否成功</returns>
public static bool OperateDatasWithTransaction(List<string> sqlList)
{
SqlConnection myConnection = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnStr"]);
myConnection.Open();
SqlCommand myCommand = myConnection.CreateCommand();
SqlTransaction myTrans;
// Start a local transaction
myTrans = myConnection.BeginTransaction(IsolationLevel.ReadCommitted);
// Assign transaction object for a pending local transaction
myCommand.Connection = myConnection;
myCommand.Transaction = myTrans;
try
{
foreach (string item in sqlList)
{
myCommand.CommandText = item;
myCommand.ExecuteNonQuery();
}
myTrans.Commit();
return true;
}
catch (Exception)
{
myTrans.Rollback();
}
finally
{
myConnection.Close();
}
return false;
}