Hi all-
I have a question for you sql gurus...
I have a requirement to audit tables in a SQL Server database. The tables are dynamically created when the application creates a form and the table holds the form data. So my plan is this, I have worked out the audit table (static) and the trigger. What i'm having issues with is getting the trigger to create from within the stored procedure. So just to recap: the user creates a form in the app, this creates a table and should call this stored procedure. The stored procedure creates the trigger on that table (which begins auditing that table, inserting to the static audit table based on the table name being passed into the stored procedure).
Where im at: I can create the stored procedure. When i go to run the stored procedure, I get the errors after passing the table as a value.
In my opinion it's an error with the correct number of single ticks, but not sure.

The Code:
USE [AdventureWorks]
GO

/****** Object: StoredProcedure [dbo].[spReplaceAuditTrigger] Script Date: 12/06/2011 15:28:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER PROC spReplaceAuditTrigger( @PassedTableName as NVarchar(255) ) AS
BEGIN
DECLARE @ActualTableName AS NVarchar(255)

SELECT @ActualTableName = QUOTENAME( TABLE_NAME )
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @PassedTableName

DECLARE @sql AS NVARCHAR(MAX)
SELECT @sql = 'create trigger [' + @PassedTableName + '_ChangeTracking] on [' + @PassedTableName + '] for insert, update, delete
as

declare @bit int ,
@field int ,
@maxfield int ,
@char int ,
@fieldname varchar(128) ,
@TableName varchar(128) ,
@PKCols varchar(1000) ,
@sql varchar(2000),
@UpdateDate varchar(21) ,
@UserName varchar(128) ,
@Type char(1) ,
@PKFieldSelect varchar(1000),
@PKValueSelect varchar(1000)

select @TableName = ' + @PassedTableName + '

-- date and user
select @UserName = system_user ,
@UpdateDate = convert(varchar(8), getdate(), 112) + '' + convert(varchar(12), getdate(), 114)

-- Action
if exists (select * from inserted)
if exists (select * from deleted)
select @Type = ''U''
else
select @Type = ''I''
else
select @Type = ''D''

-- get list of columns
select * into #ins from inserted
select * into #del from deleted

-- Get primary key columns for full outer join
select @PKCols = coalesce(@PKCols + ''and'', '' on'') + '' i.'' + c.COLUMN_NAME + '' = d.'' + c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = ' + @PassedTableName + '
and CONSTRAINT_TYPE = ''PRIMARY KEY''
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

-- Get primary key fields select for insert
select @PKFieldSelect = coalesce(@PKFieldSelect+''+'','''') + '''' + COLUMN_NAME + ''''
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = ' + @PassedTableName + '
and CONSTRAINT_TYPE = ''PRIMARY KEY''
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

select @PKValueSelect = coalesce(@PKValueSelect+''+'','''') + ''convert(varchar(100), coalesce(i.'' + COLUMN_NAME + '',d.'' + COLUMN_NAME + ''))''
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = ' + @PassedTableName + '
and CONSTRAINT_TYPE = ''PRIMARY KEY''
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

if @PKCols is null
begin
raiserror(''no PK on table %s'', 16, -1, ' + @PassedTableName + ')
return
end

select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = ' + @PassedTableName + '
while @field < @maxfield
begin
select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = ' + @PassedTableName + ' and ORDINAL_POSITION > @field
select @bit = (@field - 1 )% 8 + 1
select @bit = power(2,@bit - 1)
select @char = ((@field - 1) / 8) + 1
if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in (''I'',''D'')
begin
select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field
select @sql = ''insert Audit (Type, TableName, PrimaryKeyField, PrimaryKeyValue, FieldName, OldValue, NewValue, UpdateDate, UserName)''
select @sql = @sql + '' select '''' + @Type + ''''''
select @sql = @sql + '',''' + @PassedTableName + '''''
select @sql = @sql + '','' + @PKFieldSelect
select @sql = @sql + '','' + @PKValueSelect
select @sql = @sql + '','''' + @fieldname + ''''
select @sql = @sql + '',convert(varchar(1000),d.'' + @fieldname + '')''
select @sql = @sql + '',convert(varchar(1000),i.'' + @fieldname + '')''
select @sql = @sql + '','''' + @UpdateDate + ''''''
select @sql = @sql + '','''' + @UserName + ''''''
select @sql = @sql + '' from #ins i full outer join #del d''
select @sql = @sql + @PKCols
select @sql = @sql + '' where i.'' + @fieldname + '' <> d.'' + @fieldname
select @sql = @sql + '' or (i.'' + @fieldname + '' is null and d.'' + @fieldname + '' is not null)''
select @sql = @sql + '' or (i.'' + @fieldname + '' is not null and d.'' + @fieldname + ''is null)''
exec (@sql)
end
end'

EXEC(@SQL)
END
GO

I appreciate everyone's input.

Dan

Hi all -
This is on MSSQL 2008r2, with a home grown app.

Dan

Thanks all..

This is just a bad idea all around. What is the purpose of this database?

When you do things like these, it is always a good idea to see what SQL code your program is generating. With a simple
at the end of your code, you can debug a large part of it.
To find the harder to catch bugs, run the generated code, and see what errors it produces.
Last step is debugging the functionality of the trigger itself.

Am I getting this correct?
- Your users will dynamically create tables in your database.
- And you want to extend each and every user created table with an audit trail.
- I guess they can manage the contents of their tables too from within your application.