Is there a way to get an event when there's a change in a table?

Question

Using Azure Sql Server, and in C# if that's relevant, is there a way to get an event when data in specific table changes?

In one table we need an event if any column changes in a row.

In a 2nd table we need an event only when either of 2 columns change (although any change would be ok).

And we need to know which row.

Is it possible to do this? If so, how?

Update: We need a call into our C# code for this event. A database trigger where the database can do something doesn't help, our program needs to take action on a change.

thanks - dave

Answer 1

Windows Azure SQL Database (WASD) does partially support triggers so you could roll your own auditing. See here. Here's a really simple example:

CREATE TABLE dbo.Users ( 
    userId INT IDENTITY PRIMARY KEY,
    userName VARCHAR(30) NOT NULL UNIQUE,
    dateAdded DATETIME NOT NULL DEFAULT GETDATE(),
    addedBy VARCHAR(30) NOT NULL DEFAULT 'admin',
    dateModified DATETIME NULL,
    modifiedBy VARCHAR(30) NULL,
)
GO

INSERT INTO dbo.Users ( userName )
VALUES ( 'wBob' ), ( 'David' )
GO

CREATE TRIGGER dbo.utrg_Users ON dbo.Users
FOR UPDATE
AS 
BEGIN

    -- INSERT INTO ... other table
    SELECT 'old record' source, *
    FROM deleted

END
GO

UPDATE dbo.Users 
SET userName = 'wDavid'
WHERE userName = 'David'

WASD has also recently previewed an Audit feature which does include DML auditing. It's only available for the Basic, Standard and Premium editions and writes off to an audit log stored on an Azure storage account (not a conventional relational table) so you could also have a look at that. Looks like an interesting feature and includes an Excel template for quickly analysing the logs. Start here.

Answer 2

A WebJob or function in the app service could be bound to the blob storage container which contains the audit log.