SQL Change Trigger - DS3 Team Server Feature

Yesterday we blogged about the CRM Trigger that enables real-time synchronisation with Ouvvi / Data Sync Team Server. Today we're going to cover off the other side where you can trigger a project to start from a change in your SQL Database (SQL Server, Oracle, DB2, MySQL, ODBC, OleDB etc).

This works in a similar way to the CRM Trigger except this time we query your SQL Database every 30 seconds and ask for the latest DateTime value of any changes.

To do this you need to write a query that would return the latest change timestamp from your database for the items your monitoring.

For example

SELECT MAX(modifiedon) FROM dbo.AccountBase

To set this up you Add a new Trigger to your Data Sync Team Server environment.

Choose the SQL Trigger Option

Then configure the Trigger, choosing the Network Library and connection string to your database and the Query to execute. (Yes you can use other database servers i.e. Oracle, IBM DB2, etc).

That's it Ouvvi will start to Query your SQL DB every 30 seconds and when there's a change projects linked to this trigger will be queued to start.

Now that the project is using a Trigger you can see under Reporting how the project is started through distribution of actual changes rather than a schedule.

If your Table does not have a valid DateTime column that gets updated when the record is changed then you can implement a solution based on SQL Triggers to maintain an updated timestamp value like this.

Create a new SQL Table like this in your Database:

CREATE TABLE [dbo].[OuvviTriggers] (
    [ID] int IDENTITY(1, 1) NOT NULL,
    [Name] nvarchar(255) NOT NULL,
    [Updated] datetime DEFAULT(getdate()) NOT NULL    
)
GO

Then you can create a SQL Trigger on any Table that you want to monitor like this where you update the 'TableA' to match the name of the Table your monitoring.

CREATE TIRGGER TableA_OuvviTrigger
ON [TableA]
FOR INSERT, UPDATE, DELETE
AS
    UPDATE [dbo].[OuvviTriggers]
        SET [Updated] = GETDATE()
    WHERE [Name] = 'TableA'
GO

Then your Ouvvi Trigger just becomes:

SELECT [Updated] FROM [OuvviTriggers] WHERE [Name] = 'TableA'