Have you ever had a lot of tables in a database that needed to have the timestamp of the record updated on insert or update? I know I have. I wish I could find a really good tool for automating some of the database development tasks that I perform. Until then, I end up writing my own scripts or code.
This code utilizes the .Net SMO object model to automatically create triggers for tables that have an Modified datetime column.
Below you will see my code for adding triggers to a MS SQL server database. These triggers will update the "Modified" column in the table when a record is inserted or updated. Feel free to modify the code to fit your specific needs as it is common for the "Modified" column to be named differently per database/project.
Limits:
1. Table can only have one primary key column
Enjoy!
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
namespace Pixelect.MsSql
{
public class ModifiedDateTriggerGenerator
{
public void Process(string database)
{
// get connection string
string conString = ConfigurationManager.ConnectionStrings["SqlServerConnectionString"].ConnectionString;
// create sql connection
SqlConnection sqlConnection = new SqlConnection(conString);
// create server connection
ServerConnection serverConnection = new ServerConnection(sqlConnection);
// create server
Server server = new Server(serverConnection);
//
if (server.Databases.Contains(database))
AddModifiedTriggerToTables(server.Databases[database]);
else
throw new Exception("Database not found.");
}
private static void AddModifiedTriggerToTables(Database database)
{
foreach (Table table in database.Tables)
{
if(table.Columns.Contains("ModifiedDate"))
{
// check if table already had trigger, drop it
if (table.Triggers.Contains(string.Format("trg{0}SetModifiedDate", table.Name)))
table.Triggers[string.Format("trg{0}SetModifiedDate", table.Name)].Drop();
// set up trigger
Trigger trigger = new Trigger(table, string.Format("trg{0}SetModifiedDate", table.Name));
trigger.TextMode = false;
trigger.Insert = true;
trigger.Update = true;
// get trigger resource
string textBody = TriggerTemplate.ResourceManager.GetString("ModifiedDateTrigger");
// get template
if(string.IsNullOrEmpty(textBody))
throw new Exception("Unable to get Modified trigger template from resource.");
// swap values
textBody = textBody.Replace("[table]", string.Format("[{0}]", table.Name));
textBody = textBody.Replace("[schema]", string.Format("[{0}]", table.Schema));
// get primary key of table
List<Column> keys = GetPrimaryKeys(table);
// i.[primaryKey] = [schema].[table].[primaryKey]
string keyChain = string.Empty;
foreach (Column col in keys)
keyChain += string.Format("i.{0} = [{1}].[{2}].[{3}] AND ", col.Name, table.Schema, table.Name, col.Name);
// replace body
keyChain = keyChain.Substring(0, keyChain.Length - 5);
textBody = textBody.Replace("[keys]", keyChain);
// update body
trigger.TextBody = textBody;
// create trigger
trigger.Create();
}
}
}
private static List<Column> GetPrimaryKeys(Table table)
{
List<Column> keys = new List<Column>();
foreach (Column c in table.Columns)
if (c.InPrimaryKey) keys.Add(c);
return keys;
}
}
}