In this day and age of cost-cutting layoffs and job insecurity, you've probably found yourself in some challenging situations. Recently, I found myself in the position of being the sole developer of a .NET application using SQL Server 2000. So I did my best to design a solid database using my limited knowledge of database design. It was around that time that I finally started to appreciate a good DBA!

I had designed a database with about 20 tables, and for auditing sake, each table had two fields, DateCreated and DateModified, to track when each record was created and modified. I was then presented with a challenge: How should I populate these fields? Should I populate them in every insert and update routine I write? That sounded burdensome. A better idea would be to use Insert and Update triggers. That means defining two triggers for each table in my database, a total of 40 triggers! I sure didn't feel like doing that manually, so I decided to investigate how to automatically generate them. And since I was certainly not a SQL guru, I decided to see if I could do it in .NET.

Triggers
Let's take a look at what the Insert trigger should 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 trigger will run after a record is inserted in tablename. The first challenge we face is locating the proper record to update, the record that was just inserted into the table. If the affected table's primary key is only one field, we could select the @@Identity value from our table. But that will not work with tables with more than one field defined as the primary key. Luckily, 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 and UPDATE statements. During an insert or update transaction, new rows are added simultaneously to both the inserted table 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 Update trigger will look identical to the Insert trigger, except that DateCreated will not be updated, only DateModified.

Creating the C# Program
I created a Windows application in Visual Studio .NET and used the default form1.cs. I dropped a textBox (named tbOutput) and a Button (named cmdCreateTriggers) on the form.

I then opened the code window and added the following using statements at the top of the file:

using System.Data;
using System.Data.SqlClient;

I then created a click event for cmdCreateTriggers, and declared my variables:

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;

Accessing the INFORMATION_SCHEMA
First, we need to retrieve a list of all the user-defined tables in our database, along with their primary keys defined. The best way to do this is to access the INFORMATION_SCHEMA views provided by SQL Server. These views provide developers with a way to view the metadata of a SQL Server database. Among the many views are the two that we need. First, the TABLE view, which lists all the tables in a particular database. The second view we need is the KEY_COLUMN_USAGE table, which lists all the primary keys that exist in a particular database.

In our code, we will create a DataSet object that will hold these two views. For our Tables view, we only want to select tables that were defined by the users of this database, not system tables or any other user tables that may have been created by Microsoft. Below is the SQL Query that will select these tables:

sqlAllTables = "SELECT " +
"Table_name from INFORMATION_SCHEMA.TABLES " +
"WHERE Table_type = 'BASE TABLE' " +
"AND OBJECTPROPERTY
(OBJECT_ID(TABLE_NAME)" +
",'IsMSShipped')=0";

The Table_type field value for user-defined tables is "Base Table" and the second part of the where clause is used to filter out any tables that Microsoft ships with versions of SQL Server. Listing 1 shows the SQL to retrieve all of the primary keys defined in the database.

Note that in selecting the primary keys, we did not filter based on the Table_type; we will see later that this isn't necessary. Now let's open up a connection to the database:

connectionString = "myconnectionstring";
SqlConnection con = new SqlConnection(connectionString);
con.Open();

Fill in your database connection string or better yet, create a field on the form that will ask the user to fill in the connection string. Now we need to create the DataSet and populate it with the data resulting from our SQL queries (see Listing 2).

We declare the DataSet, then create a SqlDataAdapter that contains the result of the Tables query. We then add that table to the DataSet. Next, we change the Select Command.CommandText property, whose value is the Tables query, to contain the Primary Keys query. Then we add that table to the DataSet. Next, we create a DataTable object for each of the tables for use in the next command:

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

The Relations.Add method of the DataSet object allows the developer to create data relations between tables contained in the DataSet. In this case, we need to create a parent-child relationship between the Tables table and the Primary Keys table, so that when we later loop through the Tables table, we can quickly and easily find the related child rows in the Primary Keys table. ADO 2.x Data Shaping is not supported in ADO.NET, but working with Relations in the DataSet object is very similar.

Now that we have our list of tables that need triggers, let's loop through those tables and create the CREATE TRIGGER statements. The code for this is shown in Listing 3.

First, we create the SQL code to drop the triggers in case they exist. Then we populate the sqlInsert and sqlUpdate strings with the beginning of the CREATE TRIGGER statement discussed earlier. Then we loop through the keys for the current table using this foreach loop:

foreach (DataRow childRow in
parentRow.GetChildRows
("Tables_Keys"))

We use the GetChildRows method of the parentRow object, which takes one argument, the name of the DataRelation that we created earlier in the code. The method returns a collection of DataRow objects that represent all the fields that make up the primary key for the current table. We loop through those 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 the text box and move on to the next table. You could very easily pipe these statements to a .sql file, or you could even execute them one by one using the SqlConnection.

Gotcha!
If you use the SQL code generated by the application defined above to create triggers in your SQL Server database, it will work - most of the time. There is a problem with the triggers we've created. If a user performs a multiple row INSERT or UPDATE, our triggers will fail. This is because the triggers we've built assume that there is one record in the inserted table. When there is more than one record in the inserted table, SQL Server will generate an error when running the trigger. The solution to this problem is to create a cursor on the inserted table so that you can iterate through the table and update each record that was inserted or updated. If you know how to code some SQL, creating this cursor is not difficult, but it would have caused the code in this article to be bulky and it didn't add anything to the base concepts of this article. To see the complete application, download the code from www.sys-con.com/dotnet/sourcec.cfm.

Conclusion
While you may never need to create triggers programmatically, this article has shown you how to use the SQL Server INFORMATION_SCHEMA views to access your databases' metadata. This article has also outlined some powerful aspects of ADO.NET, including how to work with multiple tables in a DataSet object. I hope that you can apply some of these concepts to future projects!

• Source Code