Using Triggers in C# and Sql Server

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

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