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

MicroSoft SQL Server column expressions can simplify some of the logic in your database schema or application logic by allowing expressions to derive the value of a database table column.

For example;

I was needing to perform some basic calculations for order line items. Rather than creating a trigger to multiple the QTY ordered by the selling PRICE of the item and place that value in the TOTAL column, I used an expression in the TOTAL column to do this multiplication for me. Here is a screenshot.


 
After this setup on the column, you will find that all records will have the proper order total automatically. No need to code triggers, put in application logic to calculate and update the value.

In real world usage I am sure it will not be this simple. You will want to make careful choices about what belongs in the column spec. or in the business rule logic.

Best of luck.