可以在C＃中以编程方式创建SQL触发器。每次在数据库中的表上发生插入，更新或删除时，触发器都将运行。 SQL Server提供了两个特殊的只读，内存驻留表，插入和已删除，以测试某些数据修改的效果并设置触发操作的条件。



在我们的Insert触发器中，我们使用inserted表，它在INSERT，UPDATE和DELETE语句期间存储受影响行的副本。在插入或更新事务期间，新行将同时添加到插入的表和触发器表中。在删除事务期间，将从inserted表和触发器表中删除行。因此，通过在目标表和插入表中编写指定主键字段匹配的where子句，我们可以更新目标表中的特定记录。



Insert触发器看起来像 -

SQL trigger can be created programmatically in C#. The trigger will run each time an insert, update or delete occurs on table(s) in the database. SQL Server provides two special read-only, memory resident tables, inserted and deleted, to test the effects of certain data modifications and to set conditions for trigger actions.

In our Insert trigger, we use the inserted table, which stores copies of the affected rows during INSERT, UPDATE and DELETE statements. During an insert or update transaction, new rows are added simultaneously to both the inserted table and the trigger table. During a delete transaction, rows are removed both from the inserted and the trigger table. So by writing a where clause that specifies the primary key field matches in our target table and the inserted table, we are able to update the specific record in our target table.

The Insert trigger will look like -

CREATE TRIGGER trg_insert_tablename
ON tablename FOR INSERT AS
UPDATE tablename SET DateCreated=GetDate(),
DateModified=GetDate()
WHERE (tablename.primarykeyfield1 =
(select primarykeyfield1 from inserted)
AND ...( tablename primarykeyfieldn =
(select primarykeyfieldn from inserted) )

Update触发器看起来与Insert触发器相同，但DateCreated不会更新，只有DateModified。



首先在Visual Studio中创建一个Windows应用程序。在默认的Form.cs页面中添加一个按钮（名为 cmdCreateTriggers ）。为此按钮添加OnClick事件并将其指向cmdCreateTriggers_click（）fucntion。在这个函数中，我们将编写SQL触发器的代码。

The Update trigger will look identical to the Insert trigger, except that DateCreated will not be updated, only DateModified.

First create a windows application in Visual Studio. In the default Form.cs page add a button(named cmdCreateTriggers). Add an OnClick event for this button and point it to cmdCreateTriggers_click() fucntion. In this function we will write the code for the SQL trigger.

private void cmdCreateTriggers_click
(object sender, System.EventArgs e)
{
string sqlInsert;
string sqlUpdate;
string sqlDropTriggers;
string sqlWhere;
string tableName;
string sqlAllTables;
string sqlPrimaryKeys;
string strConn;

//Using INFORMATION_SCHEMA to view the metadata of a SQL Server database
//TABLE view lists all the tables in a particular database
//KEY_COLUMN_USAGE table lists all the primary keys that exist in a particular database

//Filter out any tables that Microsoft ships with versions of SQL Server
sqlAllTables = "SELECT Table_name from INFORMATION_SCHEMA.TABLES " +
               "WHERE Table_type = 'BASE TABLE' " +
               "AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME),'IsMSShipped')=0";

//Set value of myconnectionstring to your connection string
string connectionString = "myconnectionstring";
SqlConnection con = new SqlConnection(connectionString);
con.Open();

//Declare a DataSet
DataSet dsTrigger = new DataSet();
DataTable dtTables = new DataTable();

dsTrigger.Relations.Add("Tables_Keys",
dtTables.Columns["TABLE_NAME"],
dtKeys.Columns["TABLE_NAME"]);

//We have a list of tables that need triggers, let's loop through those tables and create the CREATE TRIGGER statements

foreach (DataRow childRow in
parentRow.GetChildRows("Tables_Keys"))
{
      //Loop through the rows, accessing the column_name property of the childRow object, to build the complete WHERE clause for the trigger.
                  
}
//Once the complete CREATE TRIGGER statement is built, we simply output the SQL statements to a .sql file
TextWriter outSql = new StreamWriter("trigger.sql");
outSql.Write(content);
outSql.Close();

我认为不可能。而且我不确定你为什么要从你的代码中调用触发器。触发器在更新，插入或删除期间自动启动。因此，您的触发器应在更新后自动执行。

I don''t think its possible. And I''m not sure why you would want to call a trigger from your code. Triggers automatically initiate during update, insert or delete. So your trigger should automatically execute after your update.

以如何创建触发器？ [ ^ ]。