Hello,
To have 100%
DB detection you will have to implement triggers etc...
You can use "fake" SQL Statements but the detection will never cover all changes (like deletes etc..).
(Combine these statemens with scheduled "Load All" to sync all changes)
Example 1:Sync only last (1 day) inserts/updates (YOU WILL MISS DELETES!):
SELECT myID as TranNr, 'i' as TranType, myID FROM MyTable where LastModification > DATEADD(day, -1, GETDATE())
(myID=your primary key)
Example 2:Using stored procedure storing last sync date/time:
Sample table for storing last sync date/time:
CREATE TABLE MyLastSyncTable(LastSync datetime)
CREATE PROCEDURE spMyGetChanges
AS
BEGIN
SET NOCOUNT ON
declare @nLastSync datetime
select @nLastSync=MAX(LastSync) from MyLastSyncTable
if ( @nLastSync IS NULL)
BEGIN
//remember last sync date
INSERT INTO MyLastSyncTable VALUES(GETDATE())
//Force refresh of all rows (Load All)
SET NOCOUNT OFF
//Force full refresh (Load All)
SELECT 1 as TranNr, 'r' as TranType, 1 as myID
END
else
BEGIN
UPDATE MyLastSyncTable SET LastSync=GETDATE()
where LastSync = @nLastSync
SET NOCOUNT OFF
SELECT myID as TranNr, 'i' as TranType, myID FROM MyTable where LastModification > @nLastSync
END
END
Please note:
This is only an example, you will have to modify the code based on your datamodel and database used!
If you are syncing multiple users, you must extend the example with user or folder identification:
CREATE TABLE MyLastSyncTable(LastSync datetime, UserOrFolder varcharXX) etc....