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.
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:
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.
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 a 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.
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
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.
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
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
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
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
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.
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);
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
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
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.