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;
sqlAllTables = "SELECT Table_name from INFORMATION_SCHEMA.TABLES " +
"WHERE Table_type = 'BASE TABLE' " +
"AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME),'IsMSShipped')=0";
string connectionString = "myconnectionstring";
SqlConnection con = new SqlConnection(connectionString);
con.Open();
DataSet dsTrigger = new DataSet();
DataTable dtTables = new DataTable();
dsTrigger.Relations.Add("Tables_Keys",
dtTables.Columns["TABLE_NAME"],
dtKeys.Columns["TABLE_NAME"]);
foreach (DataRow childRow in
parentRow.GetChildRows("Tables_Keys"))
{
}
TextWriter outSql = new StreamWriter("trigger.sql");
outSql.Write(content);
outSql.Close();