All the Changes, All the Time: Part I

All the Changes, All the Time: Part I

his 10-Minute Solution was prompted by a question to the Ask the SQL Pro site regarding how best to capture changes that have occurred in a database. I will address this recurring issue by sharing some work I’ve recently completed for a client. This article assumes that you are familiar with coding triggers. If you’ve never coded or used a trigger, you should first read my previous 10-Minute Solutions on the subject (they’re called “The Trouble with Triggers,” Part I, Part II, and Part III). This part of my Solution covers capturing the changes; Part II will cover applying them to another database.

The Situation
My client has a database that contains information about companies for use in an e-commerce site. The information enters the database via an administrative Web site that allows a company’s personnel to add and edit the information regarding their company. This information is viewed by the public in another portion of the Web site. These changes are not made directly to the production database. Rather, they’re made in an “Edit” database. Before any change is actually moved to the production database, an administrator from each company is given the option of reviewing each change. Only approved changes are moved to the production database.

The information is contained in approximately 25 tables with over 400 columns. What would be the best way of capturing the changes? How could the changes be stored in one location? How would we move the changes to production?

I am not averse to using someone else’s code to solve a problem. I did a quick search to see if there were any tools on the market that would fulfill my objectives. I found several tools that would automate the building of triggers for capturing changes (e.g., SQL Audit from Red Matrix Technologies). However, no tool was well-suited to my purposes.

Here’s the solution I came up with.

Storing the Data
In order to simplify matters, I decided to store an audit trail of all changes to the database in one central table. I needed to be able to store every type of column in my table, so I stored the old and new values in the text fields (included in my database). Columns from other data types would then have to be converted to character format before they could be stored in the audit table. The structure of the audit table I created is illustrated in Table 1 below.

Table 1. Audit Table Structure

Column

Type

Description

IID

Integer

Auto generated primary key

CHANGE DATE

Datetime

Date change made

COMPANY ID

Integer

ID of company whose information was changed

ROW ID

Integer

Primary key of row that was changed; all tables in the database use a single integer column for the primary key

ROW DESC

Varchar(500)

A description of the row, suitable for display to the person reviewing the changes

CHANGE TYPE

CHAR(10)

INSERTED, DELETED, or UPDATED

FIELD NAME

Varchar(300)

Name of field that was changed. (blank or null for inserted or deleted)

TABLE NAME

Varchar(300)

Name of table that was changed

USER NAME

Varchar(300)

Name of user who changed the information

OLD_VALUE

Text

Value before change

NEW_VALUE

Text

Value after change

PROCESS STATUS

Int

0 if not moved to production database; 1 if moved

Capturing the Changes
In order to capture the changes, I created a trigger on each table that would write the changes to the audit table. Triggers are used as they are only executed in response to changes to information. They also provide a view of the data before and after the change, so the old and new values are captured with ease.

The following example illustrates the ease with which a trigger for an insert action may be created:

create trigger ti_company on company for insertasdeclare @blank charselect @blank = ”insert into auditlog(companyid, rowid, changetype, tablename, fieldname, old_value, new_value, username, rowdesc)select i.iid, i.iid, ‘INSERTED’, ‘company’, @blank, @blank, @blank, i.username, ‘Company: ‘ +i.CompanyNamefrominserted I
Once I built one trigger, it was relatively simple to generate insert triggers for each table. I just changed the name of the table and the row descriptor for each table. Similarly, delete triggers can be built from the same template by changing the words INSERT and INSERTED to DELETE and DELETED, respectively.

The Update Trigger
An update trigger should not be much more difficult to create. However, in practice, I was faced with what appeared to be a very repetitive, long, and mistake-prone task. First, let’s examine the code to store an update of a single column in the database:

if update( IsExpectingFunding)insert into auditlog(companyid, rowid, changetype, username, tablename, fieldname, old_value, new_value , rowdesc)select i.iid, i.iid, ‘UPDATED’, i.username, ‘company’, ‘IsExpectingFunding’,cast(d.IsExpectingFunding as char), cast(i.IsExpectingFunding as char), ‘Company: ‘ +i.CompanyNamefrominserted i,deleted dwhere i.iid = d.iid
This snippet of code begins by calling the IF UPDATE function to check if any change has been made to the field in question. If such a change has been made, a row with the appropriate information is inserted into the audit log. As you can see, the old and new values are retrieved from the virtual inserted and deleted tables respectively. (If you’ve never heard of these tables, see my related 10-Minute Solutions on triggers.)

Generating this code for every column in the database makes this a difficult task. Even with cutting and pasting, it’s a laborious and error-prone process.

Using Information Schema to Build the Trigger
For a given table, the only necessary difference between the statements for each column was the column name itself. I decided to write a cursor that would traverse the information_schema.columns view and print out the appropriate statements for each column. Thus, my trigger was built. The information_schema views are a set of views that expose metadata about your database. The columns view has a row for every column in your database. Each row includes such information as the table name and data type. (For more information on INFORMATION_SCHEMA, see my related 10-Minute Solution, “INFORMATION_SCHEMA: A Map to Your Database“.)

Basically, I used string variables to store the necessary portions of the statements, including the insert portion, the from clause, and the row descriptor. For each column, I combined the various clauses with the proper column name in one select statement. Following is the code I used for a table in my database called “patents”:

godeclare @table_name varchar(30), @column_name varchar(50), @data_type char(30), @from varchar(2048), @insert varchar(2048), @values varchar(2048), @rowid varchar(250)  set @insert = ‘insert into auditlog( companyid, rowid, changetype, username, tablename, fieldname, old_value, new_value , rowdesc)’select @rowid = “‘Patent Name: ‘ + i.patentname”select @values = “select i.fk_company, i.iid, ‘UPDATED’, i.username, “select @from = ‘frominserted i,deleted dwhere i.iid = d.iid’select @table_name = ‘patents’declare mycolumns cursor for select column_name,data_typefrom information_schema.columnswheretable_name = @table_name andcolumn_name <> ‘IID’ andcolumn_name not like ‘fk%’ order by data_type– iid is the primary key and columns that begin with fk are used for — foreign keys. No one edits these fields.open mycolumnsfetch next from mycolumns into @column_name,@data_typewhile @@fetch_status = 0begin   select ‘/* ‘ + @column_name + ‘ ‘+@data_type + ‘*/’ + char(13) +  ‘if update( ‘ + @column_name + ‘)’ + char(13) + @insert + char(13)  + @values + “‘” + @table_name + “‘,”+ “‘” + @column_name + “‘” + ‘,cast(d.’ + @column_name + ‘ as char), cast(i.’ + @column_name + ‘ as char),’ + @rowid + @from   fetch next from mycolumns into @column_name,@data_typeendclose mycolumnsdeallocate mycolumns
Here is a portion of the results. Other than the “create trigger” statement that I need to attach to the beginning, I am done. I also inserted “char(13)” for a carriage return at appropriate places so the output would be readable.
/* IsPatentIssued bit */if update( IsPatentIssued)insert into auditlog( companyid, rowid, changetype, username, tablename, fieldname, old_value, new_value, rowdesc)select i.fk_company, i.iid, ‘UPDATED’, i.username, ‘patents’, ‘IsPatentIssued’, cast(d.IsPatentIssued as char), cast(i.IsPatentIssued as char), ‘Patent Name: ‘ + i.patentnamefrominserted i,deleted dwhere i.iid = d.iid/* FilingDate datetime */if update( FilingDate)insert into auditlog( companyid, rowid, changetype, username, tablename, fieldname, old_value, new_value, rowdesc)select i.fk_company, i.iid, ‘UPDATED’, i.username, ‘patents’, ‘FilingDate’, cast(d.FilingDate as char), cast(i.FilingDate as char), ‘Patent Name: ‘ + i.patentnamefrominserted i,deleted dwhere i.iid = d.iid
I could have replaced the cursor with a simple select statement from information_schema. But when I originally wrote the code, I thought I would have different processing for each data type or would be using more than one select statement. Therefore, it was more appropriate to use a cursor. By the time I realized I didn’t need the cursor, the code was already working.

Query Analyzer Setting
When I first ran my code, I only saw part of the trigger statement that should have been generated. It appeared chopped off. As it turns out, there is a query analyzer option that controls the maximum number of characters displayed per column. The option is set to 256 characters by default. This setting can be changed by choosing Query > Current Connections Options and choosing the table titled Advanced. There you can increase the value.

Text Fields Cause Problems
With the above code, it was simple to generate triggers and to plug in different table names, the necessary joins, and the row descriptors. However, I ran into a problem as soon as I hit a table that contained a text field. Text fields cannot be referenced in the inserted or deleted tables. If this is attempted, SQL Server returns an error. I therefore needed to obtain the new value by referencing the actual table in which the change had been made. But in order to obtain the old value, I therefore needed to go back to the production database and obtain the original row before any edits had been made. The following snippet of code from a trigger on the “websites” table illustrates this point:

if update(url)insert into auditlog( companyid, rowid, changetype, tablename, fieldname, old_value, new_value, username, rowdesc)select i.fk_company, i.iid, ‘updated’, ‘websites’, ‘url’, old.url,new.url ,i.username, ‘url: ‘ + i.url frominserted iinner join companyedit.dbo.websites newon(i.iid = new.iid)left joincompanyinfo.dbo.websites oldon(i.iid = old.iid)
I was careful to use a left join for the old value. This was to protect against the insertion of a new row into the edit database followed by an update. The “old” value would not be found in the production database. An inner join would have caused the select statement to return no rows, leaving no audit trail. Using a left join, I created something of an audit trail even if I couldn’t record the old value.

In Part II, I will describe how I used dynamic SQL statements to move changes from the audit log to the production database.

devx-admin

devx-admin

Share the Post:
USA Companies

Top Software Development Companies in USA

Navigating the tech landscape to find the right partner is crucial yet challenging. This article offers a comparative glimpse into the top software development companies

Software Development

Top Software Development Companies

Looking for the best in software development? Our list of Top Software Development Companies is your gateway to finding the right tech partner. Dive in

India Web Development

Top Web Development Companies in India

In the digital race, the right web development partner is your winning edge. Dive into our curated list of top web development companies in India,

USA Web Development

Top Web Development Companies in USA

Looking for the best web development companies in the USA? We’ve got you covered! Check out our top 10 picks to find the right partner

Clean Energy Adoption

Inside Michigan’s Clean Energy Revolution

Democratic state legislators in Michigan continue to discuss and debate clean energy legislation in the hopes of establishing a comprehensive clean energy strategy for the

Chips Act Revolution

European Chips Act: What is it?

In response to the intensifying worldwide technology competition, Europe has unveiled the long-awaited European Chips Act. This daring legislative proposal aims to fortify Europe’s semiconductor

USA Companies

Top Software Development Companies in USA

Navigating the tech landscape to find the right partner is crucial yet challenging. This article offers a comparative glimpse into the top software development companies in the USA. Through a

Software Development

Top Software Development Companies

Looking for the best in software development? Our list of Top Software Development Companies is your gateway to finding the right tech partner. Dive in and explore the leaders in

India Web Development

Top Web Development Companies in India

In the digital race, the right web development partner is your winning edge. Dive into our curated list of top web development companies in India, and kickstart your journey to

USA Web Development

Top Web Development Companies in USA

Looking for the best web development companies in the USA? We’ve got you covered! Check out our top 10 picks to find the right partner for your online project. Your

Clean Energy Adoption

Inside Michigan’s Clean Energy Revolution

Democratic state legislators in Michigan continue to discuss and debate clean energy legislation in the hopes of establishing a comprehensive clean energy strategy for the state. A Senate committee meeting

Chips Act Revolution

European Chips Act: What is it?

In response to the intensifying worldwide technology competition, Europe has unveiled the long-awaited European Chips Act. This daring legislative proposal aims to fortify Europe’s semiconductor supply chain and enhance its

Revolutionized Low-Code

You Should Use Low-Code Platforms for Apps

As the demand for rapid software development increases, low-code platforms have emerged as a popular choice among developers for their ability to build applications with minimal coding. These platforms not

Cybersecurity Strategy

Five Powerful Strategies to Bolster Your Cybersecurity

In today’s increasingly digital landscape, businesses of all sizes must prioritize cyber security measures to defend against potential dangers. Cyber security professionals suggest five simple technological strategies to help companies

Global Layoffs

Tech Layoffs Are Getting Worse Globally

Since the start of 2023, the global technology sector has experienced a significant rise in layoffs, with over 236,000 workers being let go by 1,019 tech firms, as per data

Huawei Electric Dazzle

Huawei Dazzles with Electric Vehicles and Wireless Earbuds

During a prominent unveiling event, Huawei, the Chinese telecommunications powerhouse, kept quiet about its enigmatic new 5G phone and alleged cutting-edge chip development. Instead, Huawei astounded the audience by presenting

Cybersecurity Banking Revolution

Digital Banking Needs Cybersecurity

The banking, financial, and insurance (BFSI) sectors are pioneers in digital transformation, using web applications and application programming interfaces (APIs) to provide seamless services to customers around the world. Rising

FinTech Leadership

Terry Clune’s Fintech Empire

Over the past 30 years, Terry Clune has built a remarkable business empire, with CluneTech at the helm. The CEO and Founder has successfully created eight fintech firms, attracting renowned

The Role Of AI Within A Web Design Agency?

In the digital age, the role of Artificial Intelligence (AI) in web design is rapidly evolving, transitioning from a futuristic concept to practical tools used in design, coding, content writing

Generative AI Revolution

Is Generative AI the Next Internet?

The increasing demand for Generative AI models has led to a surge in its adoption across diverse sectors, with healthcare, automotive, and financial services being among the top beneficiaries. These

Microsoft Laptop

The New Surface Laptop Studio 2 Is Nuts

The Surface Laptop Studio 2 is a dynamic and robust all-in-one laptop designed for creators and professionals alike. It features a 14.4″ touchscreen and a cutting-edge design that is over

5G Innovations

GPU-Accelerated 5G in Japan

NTT DOCOMO, a global telecommunications giant, is set to break new ground in the industry as it prepares to launch a GPU-accelerated 5G network in Japan. This innovative approach will

AI Ethics

AI Journalism: Balancing Integrity and Innovation

An op-ed, produced using Microsoft’s Bing Chat AI software, recently appeared in the St. Louis Post-Dispatch, discussing the potential concerns surrounding the employment of artificial intelligence (AI) in journalism. These

Savings Extravaganza

Big Deal Days Extravaganza

The highly awaited Big Deal Days event for October 2023 is nearly here, scheduled for the 10th and 11th. Similar to the previous year, this autumn sale has already created

Cisco Splunk Deal

Cisco Splunk Deal Sparks Tech Acquisition Frenzy

Cisco’s recent massive purchase of Splunk, an AI-powered cybersecurity firm, for $28 billion signals a potential boost in tech deals after a year of subdued mergers and acquisitions in the

Iran Drone Expansion

Iran’s Jet-Propelled Drone Reshapes Power Balance

Iran has recently unveiled a jet-propelled variant of its Shahed series drone, marking a significant advancement in the nation’s drone technology. The new drone is poised to reshape the regional

Solar Geoengineering

Did the Overshoot Commission Shoot Down Geoengineering?

The Overshoot Commission has recently released a comprehensive report that discusses the controversial topic of Solar Geoengineering, also known as Solar Radiation Modification (SRM). The Commission’s primary objective is to

Remote Learning

Revolutionizing Remote Learning for Success

School districts are preparing to reveal a substantial technological upgrade designed to significantly improve remote learning experiences for both educators and students amid the ongoing pandemic. This major investment, which

Revolutionary SABERS Transforming

SABERS Batteries Transforming Industries

Scientists John Connell and Yi Lin from NASA’s Solid-state Architecture Batteries for Enhanced Rechargeability and Safety (SABERS) project are working on experimental solid-state battery packs that could dramatically change the

Build a Website

How Much Does It Cost to Build a Website?

Are you wondering how much it costs to build a website? The approximated cost is based on several factors, including which add-ons and platforms you choose. For example, a self-hosted