c# - 如何使用触发器？

Question

我有一个包含一些表的数据库，
每当在表中写入一些数据时，我想在 C# 中运行一个方法。

我知道我必须使用触发器，但我不知道怎么做!!!
如果您能指导我，我将不胜感激。

附言:
我正在使用 SQLExpress 2008

Answer 1

ADO.NET 提供了非常有用的类 SqlDependency。当数据库中指定的表发生变化时，您可以订阅 OnChange 事件。

这是文档和示例:

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

它看起来很不错，而且很好用!

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);
}

但是，您必须在数据库中启用 Service Broker 才能接收这些通知。以下是如何做到这一点:

--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