Browse DevX
Sign up for e-mail newsletters from DevX


The Baker's Dozen: 13 Productivity Tips for Database Development Using Transact-SQL : Page 5

This installment of "The Baker's Dozen" presents a variety of real-world database situations and how you can use Transact-SQL and SQL-92 to tackle these issues.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Tip 11: Use UPDATE Triggers to Implement Audit Trail Changes
Requirement: You need to log changes to specific columns to an audit trail log. The log should contain the table modified, the primary key of the row modified, the name of the column modified, the values before and after the change (old value/new value), and the date/time of the update.

Many clients want to track changes to specific database items. UPDATE triggers allow you to implement audit trails by providing the INSERTED and DELETED tables that you used in the previous tip.

In Listing 9, you use an UPDATE trigger on a Product Master table to track changes to an item by querying the INSERTED and DELETED tables for changes to particular columns. For this example, check the PRICE and DESCRIPTION columns for changes, and then write the values from INSERTED and DELETED into an application log table.

Author's Note This tip provides basic functionality for Audit Trail logging. If you want to build a completely automated audit trail solution, you should consider building a data-driven script to generate UPDATE triggers for those tables/columns where you require logging.

Several companies make products that can help you build automated audit trail solutions. I can personally recommend SQLAudit from Red Matrix Technologies which provides these capabilities (and more). Considering the amount of development effort to build a comprehensive solution for audit trail, purchasing a third-party tool like SQLAudit may be a wise economic choice.

Tip 12: Dynamic SQL
Although it's a controversial subject, dynamic SQL statements can sometimes provide value in data-driven applications where you cannot determine critical query syntax until runtime. Listing 10 demonstrates two uses of dynamic SQL. The first example constructs a simple query where the table name is a variable. The second example demonstrates how to direct the results of a query that returns one row to an output variable.

Author's Note: In Listing 10, the Unicode data (nChar and nVarChar) must be used with dynamic SQL.

You can find excellent discussions on dynamic SQL by searching for "dynamic SQL" on Google. Many online articles demonstrate a variety of dynamic SQL techniques, and some even discuss SQL injection with respect to dynamic SQL.

Tip 13: Get a List of Tables and Columns for a Database
A common beginner's question is how to obtain a list of all tables and columns for a database. You can easily query the Information Schema to retrieve this list:

-- Return a list of table names SELECT DISTINCT Table_Name FROM Northwind.INFORMATION_SCHEMA.COLUMNS ORDER BY Table_Name -- Return a list of columns -- (perform a SELECT * to see a full list) SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM Northwind.INFORMATION_SCHEMA.COLUMNS ORDER BY Table_Name -- You could combine the capability of LIKE -- to find columns with a particular search -- pattern

My Web site lists several good books on SQL Server 2000 and Transact-SQL. In addition, my Web site also lists dozens of online sources for SQL Server, Transact-SQL, SQL-92, etc.

All code listings, as well as a list of on-line resources for SQL Server 2000, can be found on my Web site as well.

This is an ongoing project, so there may be a few enhancements from time to time. Check the release notes Word document for details.

Kevin S. Goff is the founder and principal consultant of Common Ground Solutions, a consulting group that provides custom web and desktop software solutions in .NET, Visual FoxPro, SQL Server, and Crystal Reports. Kevin has been building software applications for 17 years. He has received several awards from the U.S. Department of Agriculture for systems automation. He has also received special citations from Fortune 500 companies for solutions that yielded six-figure returns on investment. He has worked in such industries as insurance, accounting, public health, real estate, publishing, advertising, manufacturing, finance, consumer packaged goods, and trade promotion. In addition, Kevin provides many forms of custom training.
Thanks for your registration, follow us on our social networks to keep up-to-date