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:
- Registering a domain name and creating a web site will not suddenly make your product appear at the top of all the search engines.
- Submitting the domain to Google will not suddenly generate thousands of hits.
- Creating a blog will not suddenly attract throngs of loyal readers.
- Sending out press releases will not get you mentioned in all of the trade journals.
- Spending money on a PR agency will not immediately get you written up in the trade journals.
- Spending money on banner ads will not generate lots of leads.
- Bidding on lots of Google AdWords will not generate lots of traffic.
- Creating a Facebook page will not get you lots of friends.
- 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
exec dbo.spSqlBuildAuditTable @TABLE_NAME;
fetch next from TABLES_CURSOR into @TABLE_NAME;
end
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
exec dbo.spSqlBuildAuditTrigger @TABLE_NAME;
fetch next from TABLES_CURSOR into @TABLE_NAME;
end
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
- builds a completely new audit table if one does not exist, or
- 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
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
set @Command = @Command + ' , ' + _
@COLUMN_NAME + ' ' + @COLUMN_TYPE + ' null' + @CRLF;
fetch next from COLUMNS_CURSOR into _
@COLUMN_NAME, @COLUMN_TYPE;
end
close COLUMNS_CURSOR;
deallocate COLUMNS_CURSOR;
set @Command = @Command + ' )' + @CRLF;
exec(@Command);
if right(@TABLE_NAME, 5) = '_CSTM' begin
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
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
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
close COLUMNS_CURSOR;
deallocate COLUMNS_CURSOR;
end
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
set @PRIMARY_KEY = 'ID_C';
end
set @AUDIT_TABLE = @TABLE_NAME + '_AUDIT';
if exists (select * from vwSqlTables where TABLE_NAME = _
@AUDIT_TABLE) begin
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
set @Command = 'Drop Trigger dbo.' + @TRIGGER_NAME;
exec(@Command);
end
if right(@TABLE_NAME, 5) <> '_CSTM' begin
if not exists (select * from dbo.sysobjects _
where id = object_id(@TRIGGER_NAME) and _
OBJECTPROPERTY(id, N'IsTrigger') = 1) begin
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
set @Command = @Command + _
' , ' + @COLUMN_NAME + @CRLF;
fetch next from COLUMNS_CURSOR _
into @COLUMN_NAME, @COLUMN_TYPE;
end
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
set @Command = @Command + _
' , ' + @TABLE_NAME + _
'.' + @COLUMN_NAME + @CRLF;
fetch next from COLUMNS_CURSOR _
into @COLUMN_NAME, @COLUMN_TYPE;
end
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
end
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
set @Command = 'Drop Trigger dbo.' + @TRIGGER_NAME;
exec(@Command);
end
if not exists (select * from dbo.sysobjects where id = _
object_id(@TRIGGER_NAME) and OBJECTPROPERTY_
(id, N'IsTrigger') = 1) begin
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;
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
set @Command = @Command + ' , ' + _
@COLUMN_NAME + @CRLF;
fetch next from COLUMNS_CURSOR into _
@COLUMN_NAME, @COLUMN_TYPE;
end
close COLUMNS_CURSOR;
set @Command = @Command + ' )' + @CRLF;
set @Command = @Command + ' select newid()' + @CRLF;
if right(@TABLE_NAME, 5) <> '_CSTM' begin
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
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
set @Command = @Command + _
' , ' + @TABLE_NAME + '.' + @COLUMN_NAME + @CRLF;
fetch next from COLUMNS_CURSOR into _
@COLUMN_NAME, @COLUMN_TYPE;
end
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
deallocate COLUMNS_CURSOR;
end
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
, 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)
, 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
