Click here to Skip to main content
Click here to Skip to main content

Programmer's Guide to Starting a Software Company and Building an Enterprise Application - Article 4

By , 27 Jul 2009
Rate this:
Please Sign up or sign in to vote.

Introduction

This is the fourth in a series of columns in which I will tell you how I started SplendidCRM Software, Inc. I hope that my entrepreneurial experience inspires you. In my opinion, the creation of a company can be a wonderful adventure.

Article 4

Just because you build it does not mean that they will come. A couple of months ago, I listened to a speaker talk about how to market a product. I could not help but laugh, because I had done everything that the speaker recommended, yet I still did not have a windfall of customers. Thankfully, this speaker was at a free event, so his less-than-helpful advice did not cost me anything.

Here is what I have learned about marketing:

  1. Registering a domain name and creating a web site will not suddenly make your product appear at the top of all the search engines.
  2. Submitting the domain to Google will not suddenly generate thousands of hits.
  3. Creating a blog will not suddenly attract throngs of loyal readers.
  4. Sending out press releases will not get you mentioned in all of the trade journals.
  5. Spending money on a PR agency will not immediately get you written up in the trade journals.
  6. Spending money on banner ads will not generate lots of leads.
  7. Bidding on lots of Google AdWords will not generate lots of traffic.
  8. Creating a Facebook page will not get you lots of friends.
  9. Creating a Twitter account and tweeting your every move will not get you lots of followers.

You have to be patient, consistent, realistic and frugal. These words of advice have been passed down through the ages and they are tried-and-true methods for success. The advice is good, even in an era of Internet Time, where the first-mover advantage is critical and everything must be ready to ship tomorrow. Marketing takes time, money and talent. If you do not have the time, money or talent, you may need to find a partner who has all of these attributes.

Don't get me wrong, you need a good domain name, you need a web site and you should submit that web site to all of the search engines. However, don't think that any one of these things is going to be the magic solution that will suddenly make you rich. Some people will say that a press release is a critical marketing tool. That may be true if you have a well-known organization, but if you are a small company, don't think that your press release will be picked up by anyone other than the automated internet services. Chances are, the trade sites and magazines where you would like to see your press release will not notice you. These trade sites are too busy publishing the steady stream of press generated by Microsoft, Cisco and Google.

My objective with this series of articles is to inspire, and while this article may seem like a downer, my objective is just the opposite. By helping you set realistic expectations, I hope that you will not get discouraged when your press release does not generate the kind of buzz you expect. I hope to encourage you to try different things. I hope to save you money by letting you know that spending ten thousand dollars on a PR agency will not guarantee success.

On the other hand, if you have been very successful at any particular aspect of marketing, please post a comment and tell us what you did. We all want to know what works and what doesn't.

Auditing

SplendidCRM takes an old-school approach to auditing in that it uses triggers in the database to track all changes to all records in a specific table. One of the big benefits to this approach to auditing is that it is very fast and very reliable. The big downside to this approach is that it uses lots of hard disk space. As hard disk space gets cheaper every year, we feel that the positive exceeds the negative.

By contrast, SugarCRM determines the auditing information by manually comparing the previous record to the current record in the PHP. Then, the SugarCRM web application stores one row for each field that has changed.

There are many reasons why we do not take this approach with SplendidCRM, but the most significant of these reasons is that the SugarCRM approach means that all data must be processed by the PHP application in order for auditing to be properly added whereas SplendidCRM allows any external application to insert or update data directly in the database and still have its auditing information properly recorded. Auditing data is stored in auditing tables that end in _AUDIT. These tables should have all the same fields as the base table, plus a few extra fields to track the auditing. For example, the following excerpt from the ACCOUNTS_AUDIT table shows the six common auditing fields used in all the auditing tables.

Create Table dbo.ACCOUNTS_AUDIT
	( AUDIT_ID             uniqueidentifier   not null
	, AUDIT_ACTION         int                not null
	, AUDIT_DATE           datetime           not null
	, AUDIT_VERSION        rowversion         not null
	, AUDIT_COLUMNS        varbinary(128)     null
	, AUDIT_TOKEN          varchar(255)       null

All of our auditing tables are generated dynamically using schema information provided by the database. If you are a database guru, then you probably already know how to get a list of all available tables and all columns within those tables. For those of you who are not gurus, I'm going to show you how it is done. As a big fan of stored procedures and views, you will see both procedures and views in the implementation.

vwSqlTables

First, let us start with a list of all available tables in the system. There are a couple of ways to get such a list. We use INFORMATION_SCHEMA because it is a technique that is shared across multiple database platforms. In our case, since we are dealing with SQL Server, we need to exclude a couple of quasi-system tables that are typically found in the list.

Create View dbo.vwSqlTables
as
select TABLE_NAME
  from INFORMATION_SCHEMA.TABLES
 where TABLE_TYPE = N'BASE TABLE'
   and TABLE_NAME not in (N'dtproperties', N'sysdiagrams')
GO

vwSqlTablesAudited

The second step is to reduce the list of tables to only those that are audited. In SplendidCRM, we audit everything, so our view of audited table starts with everything and we then exclude SplendidCRM system tables that don't require auditing. There are a bunch of system tables in SplendidCRM, but I'm going to abbreviate them here.

Create View dbo.vwSqlTablesAudited
as
select TABLE_NAME
  from vwSqlTables
 where TABLE_NAME not like N'%_AUDIT'
   and TABLE_NAME not like N'TEMP_%'
   and TABLE_NAME not in
( N'ACL_ROLES_CSTM'
, N'CAMPAIGN_TRKRS'
, N'CAMPAIGN_TRKRS_CSTM'
, N'CONTRACT_TYPES_DOCUMENTS'
, N'CURRENCIES_CSTM'
. . .
)
GO

As you can see from vwSqlTablesAudited, we exclude the auditing tables, temp tables and any other system tables. This means that any customer table our users create will automatically be fully audited.

spSqlBuildAllAuditTables

Now that we have a list of tables to be audited, we can use a cursor to loop through the list and build each audit table. The code is very straight-forward SQL as we define the cursor, open the cursor, loop through the cursor and close the cursor. The real heavy lifting is done inside another stored procedure that takes the table name as a parameter. Any time we build the audit tables, we also need to build the audit triggers, so we do that at the bottom of the procedure.

Create Procedure dbo.spSqlBuildAllAuditTables
as
  begin
	declare @TABLE_NAME varchar(80);
	declare TABLES_CURSOR cursor for
	select TABLE_NAME
	  from vwSqlTablesAudited
	order by TABLE_NAME;
	
	open TABLES_CURSOR;
	fetch next from TABLES_CURSOR into @TABLE_NAME;
	while @@FETCH_STATUS = 0 begin -- do
		exec dbo.spSqlBuildAuditTable @TABLE_NAME;
		fetch next from TABLES_CURSOR into @TABLE_NAME;
	end -- while;
	close TABLES_CURSOR;
	deallocate TABLES_CURSOR;

	exec dbo.spSqlBuildAllAuditTriggers ;
  end
GO

spSqlBuildAllAuditTriggers

The main procedure for creating the audit triggers is nearly identical, with the same kind of loop around the audited tables. This time, we just make sure that each audit table exists before trying to create the trigger.

Create Procedure dbo.spSqlBuildAllAuditTriggers
as
  begin
	declare @TABLE_NAME varchar(80);
	declare TABLES_CURSOR cursor for
	select vwSqlTablesAudited.TABLE_NAME
	  from      vwSqlTablesAudited
	 inner join vwSqlTables
	         on vwSqlTables.TABLE_NAME = vwSqlTablesAudited.TABLE_NAME + '_AUDIT'
	order by vwSqlTablesAudited.TABLE_NAME;
	
	open TABLES_CURSOR;
	fetch next from TABLES_CURSOR into @TABLE_NAME;
	while @@FETCH_STATUS = 0 begin -- do
		exec dbo.spSqlBuildAuditTrigger @TABLE_NAME;
		fetch next from TABLES_CURSOR into @TABLE_NAME;
	end -- while;
	close TABLES_CURSOR;
	deallocate TABLES_CURSOR;
  end
GO

vwSqlColumns

When building an audit table that has all the same fields and data types as the base table, we need a view to return the fields and their table. We use vwSqlColumns heavily within SplendidCRM, so our actual version of the view does a lot more, but for this purpose, we have simplified the view to just the information that we need to build an audit table. Also, instead of using the INFORMATION_SCHEMA, we go directly to the SQL Server system tables. These system tables provide information on user tables (U), stored procedures (P), views (V) and Functions (FN). It is important to note that vwSqlColumns only defines the ColumnType for data types supported by SplendidCRM. SQL Server supports many more data types and attempts to use our auditing system on an unsupported type will generate errors. The solution is to add support for all the data types that you use in your application to the vwSqlColumns view.

Create View dbo.vwSqlColumns
as
select sysobjects.name as ObjectName
     , syscolumns.name as ColumnName
     , syscolumns.colid
     , (case when syscolumns.xtype =  36 then N'uniqueidentifier'
             when syscolumns.xtype =  48 then N'tinyint'
             when syscolumns.xtype =  56 then N'int'
             when syscolumns.xtype = 127 then N'bigint'
             when syscolumns.xtype =  59 then N'real'
             when syscolumns.xtype =  62 _
		then N'float('    + cast(syscolumns.prec     as varchar) + N')'
             when syscolumns.xtype =  60 then N'money'
             when syscolumns.xtype = 104 then N'bit'
             when syscolumns.xtype = 175 _
		then N'char('     + cast(syscolumns.length   as varchar) + N')'
             when syscolumns.xtype = 167 _
		then N'varchar('  + cast(syscolumns.length   as varchar) + N')'
             when syscolumns.xtype = 231 _
		then N'nvarchar(' + cast(syscolumns.length/2 as varchar) + N')'
             when syscolumns.xtype = 239 _
		then N'nchar('    + cast(syscolumns.length/2 as varchar) + N')'
             when syscolumns.xtype =  99 then N'ntext'
             when syscolumns.xtype =  61 then N'datetime'
             when syscolumns.xtype =  34 then N'image'
             when syscolumns.xtype = 106 _
		then N'decimal('  + cast(syscolumns.prec   as varchar) + N', ' + _
		cast(syscolumns.scale as varchar) + N')'
             when syscolumns.xtype = 165 _
		then N'varbinary('+ cast(syscolumns.length as varchar) + N')'
             when syscolumns.xtype = 173 _
		then N'binary('   + cast(syscolumns.length as varchar) + N')'
             when syscolumns.xtype = 189 then N'timestamp'
        end
       ) as ColumnType
  from sysobjects
 inner join syscolumns
         on syscolumns.id = sysobjects.id
 where sysobjects.type in ('U', 'P', 'V', 'FN')
GO

spSqlBuildAuditTable

The spSqlBuildAuditTable does two things; it either 

  1. builds a completely new audit table if one does not exist, or 
  2. it adds missing fields if the audit table already exists.

The vwSqlColumns view is used extensively throughout SplendidCRM and was written to make it very easy to build the Create Table statement in that it returns the properly formatted data type in the ColumnType field. Building the Create Table statement then becomes a simple cursor that concatenates the ColumnName and the ColumnType. We like our statements to be readable, so we always add the @CRLF to each line, even though SQL Server does not care if we separate the fields with line breaks. When we debug this stored procedure, we often just replace the SQL "exec" statement with a print statement.

If the audit table already exists, then we use an outer join between the fields of the base table and the fields of the audit table to create a cursor of fields missing in the audit table. In this case, we execute an Alter Table statement for each field that needs to be added to the audit table.

Create Procedure dbo.spSqlBuildAuditTable(@TABLE_NAME varchar(80))
as
  begin
	declare @Command           varchar(8000);
	declare @AUDIT_TABLE       varchar(90);
	declare @AUDIT_PK          varchar(90);
	declare @COLUMN_NAME       varchar(80);
	declare @COLUMN_TYPE       varchar(20);
	declare @CRLF              char(2);
	
	set @CRLF = char(13) + char(10);
	set @AUDIT_TABLE = @TABLE_NAME + '_AUDIT';
	set @AUDIT_PK    = 'PKA_' + @TABLE_NAME;
	if not exists (select * from dbo.sysobjects where id = _
		object_id(@AUDIT_TABLE) and OBJECTPROPERTY_
			(id, N'IsUserTable') = 1) begin -- then
		declare COLUMNS_CURSOR cursor for
		select ColumnName
		     , ColumnType
		  from vwSqlColumns
		 where ObjectName = @TABLE_NAME
		 order by colid;
	
		set @Command = '';
		set @Command = @Command + 'Create Table dbo.' + @AUDIT_TABLE + @CRLF;
		set @Command = @Command + _
		'	( AUDIT_ID          uniqueidentifier   not null constraint ' _
		+ @AUDIT_PK + ' primary key' + @CRLF;
		set @Command = @Command + _
		'	, AUDIT_ACTION      int                not null' + @CRLF;
		set @Command = @Command + _
		'	, AUDIT_DATE        datetime           not null' + @CRLF;
		set @Command = @Command + _
		'	, AUDIT_VERSION     rowversion         not null' + @CRLF;
		set @Command = @Command + _
		'	, AUDIT_COLUMNS     varbinary(128)     null' + @CRLF;
		set @Command = @Command + _
		'	, AUDIT_TOKEN       varchar(255)       null' + @CRLF;
		open COLUMNS_CURSOR;
		fetch next from COLUMNS_CURSOR into @COLUMN_NAME, @COLUMN_TYPE;
		while @@FETCH_STATUS = 0 begin -- while
			set @Command = @Command + '	, ' + _
			@COLUMN_NAME + ' ' + @COLUMN_TYPE + ' null' + @CRLF;
			fetch next from COLUMNS_CURSOR into _
				@COLUMN_NAME, @COLUMN_TYPE;
		end -- while;
		close COLUMNS_CURSOR;
		deallocate COLUMNS_CURSOR;
		set @Command = @Command + '	)' + @CRLF;
		exec(@Command);

		if right(@TABLE_NAME, 5) = '_CSTM' begin -- then
			set @Command = 'create index IDX_' + _
			@AUDIT_TABLE + ' on dbo.' + @AUDIT_TABLE + _
			'(ID_C, AUDIT_TOKEN, AUDIT_ACTION)';
		end else begin
			set @Command = 'create index IDX_' + _
			@AUDIT_TABLE + ' on dbo.' + @AUDIT_TABLE + _
			'(ID, AUDIT_VERSION, AUDIT_TOKEN)';
		end -- if;
		exec(@Command);
	end else begin
		print 'Alter Table dbo.' + @AUDIT_TABLE + ';';
		declare COLUMNS_CURSOR cursor for
		select vwSqlColumns.ColumnName
		     , vwSqlColumns.ColumnType
		  from            vwSqlColumns
		  left outer join vwSqlColumns                   vwSqlColumnsAudit
		               on vwSqlColumnsAudit.ObjectName = _
					vwSqlColumns.ObjectName + '_AUDIT'
		              and vwSqlColumnsAudit.ColumnName = _
						vwSqlColumns.ColumnName
		 where vwSqlColumnsAudit.ObjectName is null
		   and vwSqlColumns.ObjectName = @TABLE_NAME
		 order by vwSqlColumns.colid;

		open COLUMNS_CURSOR;
		fetch next from COLUMNS_CURSOR into @COLUMN_NAME, @COLUMN_TYPE;
		while @@FETCH_STATUS = 0 begin -- while
			set @Command = 'alter table ' + @AUDIT_TABLE + _
			' add ' + @COLUMN_NAME + ' ' + _
				@COLUMN_TYPE + ' null' + @CRLF;
			print @Command;
			exec(@Command);
			fetch next from COLUMNS_CURSOR into _
					@COLUMN_NAME, @COLUMN_TYPE;
		end -- while;
		close COLUMNS_CURSOR;
		deallocate COLUMNS_CURSOR;
	end -- if;
  end
GO

spSqlBuildAuditTrigger

The spSqlBuildAuditTrigger procedure is very similar to the spSqlBuildAuditTable procedure in that we start with the list of fields and build a SQL statement. However, in the case of the audit triggers, we must drop the trigger if it already exists. We create separate insert and update triggers so that we can ensure that we are able to specify the correct AUDIT_ACTION (0 for insert, 1 for update, -1 for delete). In SplendidCRM, we don’t actually delete records, so you will not find a delete trigger, but we need to set the audit action flag properly if the DELETED field changes to 1.

The AUDIT_TOKEN is interesting in that we use this field to store the SQL Server @BIND_TOKEN. This value is unique for each transaction, so we use it to track all the records that have changed in a single transaction. The AUDIT_TOKEN becomes a critical field in our workflow engine.

Create Procedure dbo.spSqlBuildAuditTrigger(@TABLE_NAME varchar(80))
as
  begin
	declare @Command      varchar(8000);
	declare @CRLF         char(2);
	declare @AUDIT_TABLE  varchar(90);
	declare @TRIGGER_NAME varchar(90);
	declare @COLUMN_NAME  varchar(80);
	declare @COLUMN_TYPE  varchar(20);
	declare @PRIMARY_KEY  varchar(10);

	set @PRIMARY_KEY = 'ID';
	if right(@TABLE_NAME, 5) = '_CSTM' begin -- then
		set @PRIMARY_KEY = 'ID_C';
	end -- if;
	set @AUDIT_TABLE = @TABLE_NAME + '_AUDIT';
	if exists (select * from vwSqlTables where TABLE_NAME = _
				@AUDIT_TABLE) begin -- then
		set @CRLF = char(13) + char(10);
		declare COLUMNS_CURSOR cursor for
		select vwSqlColumns.ColumnName
		     , vwSqlColumns.ColumnType
		  from       vwSqlColumns
		  inner join vwSqlColumns                   vwSqlColumnsAudit
		          on vwSqlColumnsAudit.ObjectName = _
					vwSqlColumns.ObjectName + '_AUDIT'
		         and vwSqlColumnsAudit.ColumnName = vwSqlColumns.ColumnName
		 where vwSqlColumns.ObjectName = @TABLE_NAME
		 order by vwSqlColumns.colid;

		set @TRIGGER_NAME = 'tr' + @TABLE_NAME + '_Ins_AUDIT';
		if exists (select * from dbo.sysobjects where id = _
			object_id(@TRIGGER_NAME) and OBJECTPROPERTY_
			(id, N'IsTrigger') = 1) begin -- then
			set @Command = 'Drop   Trigger dbo.' + @TRIGGER_NAME;
			exec(@Command);
		end -- if;

		if right(@TABLE_NAME, 5) <> '_CSTM' begin -- then
			if not exists (select * from dbo.sysobjects _
			where id = object_id(@TRIGGER_NAME) and _
			OBJECTPROPERTY(id, N'IsTrigger') = 1) begin -- then
				set @Command = '';
				set @Command = @Command + _
					'Create Trigger dbo.' + @TRIGGER_NAME _
					+ ' on dbo.' + @TABLE_NAME + @CRLF;
				set @Command = @Command + 'for insert' + @CRLF;
				set @Command = @Command + 'as' + @CRLF;
				set @Command = @Command + '  begin' + @CRLF;
				set @Command = @Command + _
				'	declare @BIND_TOKEN varchar(255);' + @CRLF;
				set @Command = @Command + _
				'	exec spSqlGetTransactionToken _
				@BIND_TOKEN out;' + @CRLF;
				set @Command = @Command + _
				'	insert into dbo.' + @AUDIT_TABLE + @CRLF;
				set @Command = @Command + _
				'	     ( AUDIT_ID'      + @CRLF;
				set @Command = @Command + _
				'	     , AUDIT_ACTION'  + @CRLF;
				set @Command = @Command + _
				'	     , AUDIT_DATE'    + @CRLF;
				set @Command = @Command + _
				'	     , AUDIT_COLUMNS' + @CRLF;
				set @Command = @Command + _
				'	     , AUDIT_TOKEN'   + @CRLF;
				open COLUMNS_CURSOR;
				fetch next from COLUMNS_CURSOR into @COLUMN_NAME, _
						@COLUMN_TYPE;
				while @@FETCH_STATUS = 0 begin -- while
					set @Command = @Command + _
					'	     , ' + @COLUMN_NAME + @CRLF;
					fetch next from COLUMNS_CURSOR _
					into @COLUMN_NAME, @COLUMN_TYPE;
				end -- while;
				close COLUMNS_CURSOR
				set @Command = @Command + '	     )' + @CRLF;
				set @Command = @Command + _
				'	select newid()'           + @CRLF;
				set @Command = @Command + _
				'	     , 0  -- insert'      + @CRLF;
				set @Command = @Command + _
				'	     , getdate()'         + @CRLF;
				set @Command = @Command + _
				'	     , columns_updated()' + @CRLF;
				set @Command = @Command + _
				'	     , @BIND_TOKEN'       + @CRLF;
				
				open COLUMNS_CURSOR;
				fetch next from COLUMNS_CURSOR into _
						@COLUMN_NAME, @COLUMN_TYPE;
				while @@FETCH_STATUS = 0 begin -- while
					set @Command = @Command + _
					'	     , ' + @TABLE_NAME + _
					'.' + @COLUMN_NAME + @CRLF;
					fetch next from COLUMNS_CURSOR _
						into @COLUMN_NAME, @COLUMN_TYPE;
				end -- while;
				close COLUMNS_CURSOR;
				set @Command = @Command + _
				'	  from       inserted' + @CRLF;
				set @Command = @Command + _
				'	  inner join ' + @TABLE_NAME + @CRLF;
				set @Command = @Command + _
				'	          on ' + @TABLE_NAME + _
				'.' + @PRIMARY_KEY + ' = inserted.' + _
				@PRIMARY_KEY + ';' + @CRLF;
				set @Command = @Command + '  end' + @CRLF;
				exec(@Command);
			end -- if;
		end -- if;

		set @TRIGGER_NAME = 'tr' + @TABLE_NAME + '_Upd_AUDIT';
		if exists (select * from dbo.sysobjects where id = _
			object_id(@TRIGGER_NAME) and OBJECTPROPERTY_
			(id, N'IsTrigger') = 1) begin -- then
			set @Command = 'Drop   Trigger dbo.' + @TRIGGER_NAME;
			exec(@Command);
		end -- if;

		if not exists (select * from dbo.sysobjects where id = _
			object_id(@TRIGGER_NAME) and OBJECTPROPERTY_
			(id, N'IsTrigger') = 1) begin -- then
			set @Command = '';
			set @Command = @Command + 'Create Trigger dbo.' _
				+ @TRIGGER_NAME + ' on dbo.' + @TABLE_NAME + @CRLF;
			set @Command = @Command + 'for update' + @CRLF;
			set @Command = @Command + 'as' + @CRLF;
			set @Command = @Command + '  begin' + @CRLF;
			set @Command = @Command + _
			'	declare @BIND_TOKEN varchar(255);' + @CRLF;
			-- 01/09/2009 Paul.  spSqlGetTransactionToken should be 
                            -- used instead of sp_getbindtoken. 
			set @Command = @Command + _
			'	exec spSqlGetTransactionToken @BIND_TOKEN out;_
			' + @CRLF;
			set @Command = @Command + '	insert into dbo.' + _
							@AUDIT_TABLE + @CRLF;
			set @Command = @Command + '	     ( AUDIT_ID'      + @CRLF;
			set @Command = @Command + '	     , AUDIT_ACTION'  + @CRLF;
			set @Command = @Command + '	     , AUDIT_DATE'    + @CRLF;
			set @Command = @Command + '	     , AUDIT_COLUMNS' + @CRLF;
			set @Command = @Command + '	     , AUDIT_TOKEN'   + @CRLF;
			open COLUMNS_CURSOR;
			fetch next from COLUMNS_CURSOR into _
					@COLUMN_NAME, @COLUMN_TYPE;
			while @@FETCH_STATUS = 0 begin -- while
				set @Command = @Command + '	     , ' + _
						@COLUMN_NAME + @CRLF;
				fetch next from COLUMNS_CURSOR into _
						@COLUMN_NAME, @COLUMN_TYPE;
			end -- while;
			close COLUMNS_CURSOR;
			set @Command = @Command + '	     )' + @CRLF;
			set @Command = @Command + '	select newid()'           + @CRLF;
			if right(@TABLE_NAME, 5) <> '_CSTM' begin -- then
				set @Command = @Command + '	     , _
				(case inserted.DELETED when 1 _
				then -1 else 1 end) -- updated'      + @CRLF;
			end else begin
				set @Command = @Command + '	     , _
						1 -- updated'      + @CRLF;
			end -- if;
			set @Command = @Command + '	     , getdate()'         + @CRLF;
			set @Command = @Command + '	     , columns_updated()' + @CRLF;
			set @Command = @Command + '	     , @BIND_TOKEN'       + @CRLF;
			
			open COLUMNS_CURSOR;
			fetch next from COLUMNS_CURSOR into @COLUMN_NAME, _
								@COLUMN_TYPE;
			while @@FETCH_STATUS = 0 begin -- while
				set @Command = @Command + _
			'	     , ' + @TABLE_NAME + '.' + @COLUMN_NAME + @CRLF;
				fetch next from COLUMNS_CURSOR into _
						@COLUMN_NAME, @COLUMN_TYPE;
			end -- while;
			close COLUMNS_CURSOR;
			set @Command = @Command + '	  from       inserted' + @CRLF;
			set @Command = @Command + '	  inner join ' + _
						@TABLE_NAME + @CRLF;
			set @Command = @Command + '	          on ' + _
				@TABLE_NAME + '.' + @PRIMARY_KEY + _
				' = inserted.' + @PRIMARY_KEY + ';' + @CRLF;
			set @Command = @Command + '  end' + @CRLF;
			exec(@Command);
		end -- if;
		deallocate COLUMNS_CURSOR;
	end -- if;
  end
GO

Unless you really love to read SQL, you may want to see the output of these procedures. I'm going to use the PROJECT table as an example as it does not have too many fields. The spSqlBuildAuditTable stored procedure will create the PROJECT_AUDIT table and trPROJECT_Ins_AUDIT and trPROJECT_Upd_AUDIT triggers.

PROJECT_AUDIT

Create Table dbo.PROJECT_AUDIT
	( AUDIT_ID             uniqueidentifier   _
				not null constraint PKA_PROJECT primary key
	, AUDIT_ACTION         int                not null
	, AUDIT_DATE           datetime           not null
	, AUDIT_VERSION        rowversion         not null
	, AUDIT_COLUMNS        varbinary(128)     null
	, AUDIT_TOKEN          varchar(255)       null
	, ID                   uniqueidentifier   null
	, DELETED              bit                null
	, CREATED_BY           uniqueidentifier   null
	, DATE_ENTERED         datetime           null
	, MODIFIED_USER_ID     uniqueidentifier   null
	, DATE_MODIFIED        datetime           null
	, ASSIGNED_USER_ID     uniqueidentifier   null
	, NAME                 nvarchar(50)       null
	, DESCRIPTION          ntext              null
	, TEAM_ID              uniqueidentifier   null
	);

create index IDX_PROJECT_AUDIT on dbo.PROJECT_AUDIT(ID, AUDIT_VERSION, AUDIT_TOKEN);

trPROJECT_Ins_AUDIT

Create Trigger dbo.trPROJECT_Ins_AUDIT on dbo.PROJECT
for insert
as
  begin
	declare @BIND_TOKEN varchar(255);
	exec spSqlGetTransactionToken @BIND_TOKEN out;
	insert into dbo.PROJECT_AUDIT
	     ( AUDIT_ID
	     , AUDIT_ACTION
	     , AUDIT_DATE
	     , AUDIT_COLUMNS
	     , AUDIT_TOKEN
	     , ID
	     , DELETED
	     , CREATED_BY
	     , DATE_ENTERED
	     , MODIFIED_USER_ID
	     , DATE_MODIFIED
	     , ASSIGNED_USER_ID
	     , NAME
	     , DESCRIPTION
	     , TEAM_ID
	     )
	select newid()
	     , 0  -- insert
	     , getdate()
	     , columns_updated()
	     , @BIND_TOKEN
	     , PROJECT.ID
	     , PROJECT.DELETED
	     , PROJECT.CREATED_BY
	     , PROJECT.DATE_ENTERED
	     , PROJECT.MODIFIED_USER_ID
	     , PROJECT.DATE_MODIFIED
	     , PROJECT.ASSIGNED_USER_ID
	     , PROJECT.NAME
	     , PROJECT.DESCRIPTION
	     , PROJECT.TEAM_ID
	  from       inserted
	  inner join PROJECT
	          on PROJECT.ID = inserted.ID;
  end

trPROJECT_Upd_AUDIT

Create Trigger dbo.trPROJECT_Upd_AUDIT on dbo.PROJECT
for update
as
  begin
	declare @BIND_TOKEN varchar(255);
	exec spSqlGetTransactionToken @BIND_TOKEN out;
	insert into dbo.PROJECT_AUDIT
	     ( AUDIT_ID
	     , AUDIT_ACTION
	     , AUDIT_DATE
	     , AUDIT_COLUMNS
	     , AUDIT_TOKEN
	     , ID
	     , DELETED
	     , CREATED_BY
	     , DATE_ENTERED
	     , MODIFIED_USER_ID
	     , DATE_MODIFIED
	     , ASSIGNED_USER_ID
	     , NAME
	     , DESCRIPTION
	     , TEAM_ID
	     )
	select newid()
	     , (case inserted.DELETED when 1 then -1 else 1 end) -- updated
	     , getdate()
	     , columns_updated()
	     , @BIND_TOKEN
	     , PROJECT.ID
	     , PROJECT.DELETED
	     , PROJECT.CREATED_BY
	     , PROJECT.DATE_ENTERED
	     , PROJECT.MODIFIED_USER_ID
	     , PROJECT.DATE_MODIFIED
	     , PROJECT.ASSIGNED_USER_ID
	     , PROJECT.NAME
	     , PROJECT.DESCRIPTION
	     , PROJECT.TEAM_ID
	  from       inserted
	  inner join PROJECT
	          on PROJECT.ID = inserted.ID;
  end

Workflow

Now that you have seen how we create a detailed audit trail in SplendidCRM, it may interest you to know that we have built our entire workflow engine on top of this auditing system. Our workflow engine needs to be able to run when any particular field changes, and by comparing the audited record with the current record, we are able to detect these changes. This design for our auditing system allows us to create a high-performance workflow engine.

I hope that you have enjoyed this fourth article in the series. Please watch for article 5 within the next few weeks.

History

  • 27th July, 2009: Initial post

License

This article, along with any associated source code and files, is licensed under The Mozilla Public License 1.1 (MPL 1.1)

About the Author

Paul Rony
President SplendidCRM Software, Inc.
United States United States
No Biography provided

Comments and Discussions

 
QuestionI am at this stage PinmemberWizard Application13-Nov-12 15:17 
GeneralYour Marketing Ideas are WRONG! PinmemberTV Mogul3-Oct-09 13:48 
GeneralRe: Your Marketing Ideas are WRONG! PinmemberPaul Rony3-Oct-09 14:43 
GeneralYou obviuosly don't know anything about TV ! PinmemberTV Mogul5-Oct-09 10:02 
GeneralRe: You obviuosly don't know anything about TV ! PinmemberPaul Rony5-Oct-09 11:40 
GeneralTV Time is cheap, very cheap PinmemberTV Mogul5-Oct-09 13:57 
GeneralRe: TV Time is cheap, very cheap PinmemberPaul Rony5-Oct-09 15:56 
GeneralRe: TV Time is cheap, very cheap PinmemberForogar4-Jun-12 5:59 
GeneralTHANKS FOR THE ARTICLE Pinmembersognant31-Aug-09 21:30 
Generalart 5 PinmemberSebaSuper28-Aug-09 5:48 
GeneralRe: art 5 PinmemberPaul Rony20-Sep-09 11:44 
GeneralGreat Job PinmemberBasel Al-Khateeb27-Aug-09 4:05 
GeneralGreat article PinmemberMystery1236-Aug-09 22:12 
GeneralPro and Con of using triggers PinmemberPalm Island4-Aug-09 5:46 
GeneralRe: Pro and Con of using triggers PinmemberPaul Rony4-Aug-09 5:56 
GeneralThanks for sharing your inspiring entrepreneurial experience. PinmemberHemant.Kamalakar4-Aug-09 4:01 
GeneralspSqlGetTransactionToken PinmemberSebaSuper30-Jul-09 1:30 
GeneralRe: spSqlGetTransactionToken PinmemberPaul Rony30-Jul-09 18:12 
QuestionHow much functionality would you wrap in Triggers? Pinmembermatt from ne29-Jul-09 18:08 
AnswerRe: How much functionality would you wrap in Triggers? PinmemberPaul Rony30-Jul-09 18:10 
Generalsimilarly PinmemberMurat Ozyurt28-Jul-09 0:12 
GeneralAwesome! PinmemberdotNetSurfer27-Jul-09 18:00 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140415.2 | Last Updated 27 Jul 2009
Article Copyright 2009 by Paul Rony
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid