How can I use triggers ?

Question

I have a Database with some tables,
I wanna to run a method in C# whenever some data is written in the tables.

I know I have to use triggers, but I don't know how!!!
It would be appreciated if you guide me.

P.S:
I'm using SQLExpress 2008

Answer 1

I think one way to do that would be to create a function or a stored procedure written in C#. This is possible in SQL Server and DB2 for example. Then your trigger would be able to call this SQL Server function/SP.

But honestly, it does not look like a good design to me.

Answer 2

There are some options to run .NET code in the database, but personally? I wouldn't. I want my database to worry about storing and fetching data, that is all. Options:

• use a trigger (or any other approach) to populate an event queue, i.e. another table; have your C# app poll this table for things to do
• write the code in your app tier instead (i.e. run the code as part of making the data-change command)

Also consider the transactional nature of the database - it might be that even though you've done an INSERT/UPDATE, the transaction never gets committed (note that with the first approach, the insert to the second table will also be automatically rolled back at the same time, if done correctly)

Answer 3

ADO.NET provides very useful class called SqlDependency. You can subsribe to an OnChange event when specified table in database changes.

Here is documentation and example:

http://msdn.microsoft.com/en-us/library/62xk7953.aspx

It looks very nice and works!

void Initialization()
{
    // Create a dependency connection.
    SqlDependency.Start(connectionString, queueName);
}

void SomeMethod()
{
    // Assume connection is an open SqlConnection.

    // Create a new SqlCommand object.
    using (SqlCommand command=new SqlCommand(
        "SELECT ShipperID, CompanyName, Phone FROM dbo.Shippers", 
        connection))
    {

        // Create a dependency and associate it with the SqlCommand.
        SqlDependency dependency=new SqlDependency(command);
        // Maintain the refence in a class member.

        // Subscribe to the SqlDependency event.
        dependency.OnChange+=new
           OnChangeEventHandler(OnDependencyChange);

        // Execute the command.
        using (SqlDataReader reader = command.ExecuteReader())
        {
            // Process the DataReader.
        }
    }
}

// Handler method
void OnDependencyChange(object sender, 
   SqlNotificationEventArgs e )
{
  // Handle the event (for example, invalidate this cache entry).
}

void Termination()
{
    // Release the dependency.
    SqlDependency.Stop(connectionString, queueName);
}

However, you will have to enable Service Broker in your database in order to receive these notification. Here is how to do that:

--Create login if you do not have already
--Example:
--CREATE LOGIN ChangesMonitor WITH PASSWORD = 'ChangesMonitor' ;
--GO

--Create user associated with this login and
--add provilages in Management Studio tab Security->Users
--(not sure if all are needed, but for test purposes add following):
--db_datareader, db_datawriter,  db_ddladmin, db_accesadmin, db_owner, db_securityadmin



--must have exclusiveness on database
--script for database SqlDependExample and for user ChangesMonitor
ALTER DATABASE SqlDependExample SET ENABLE_BROKER
GO

GRANT CREATE PROCEDURE TO [ChangesMonitor];
GO
GRANT CREATE SERVICE TO [ChangesMonitor];
GO

GRANT CREATE QUEUE TO [ChangesMonitor];
GO

GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO [ChangesMonitor];
GO
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [ChangesMonitor];
GO

GRANT CONTROL ON SCHEMA::[dbo] TO [ChangesMonitor];
GO

GRANT IMPERSONATE ON USER::DBO TO [ChangesMonitor];
GO