December 16, 2011 at 3:42 pm
Can somebody help me put this into a stored procedure that takes a parameter (Table)...
I have it working as a trigger, but not as a SPROC.... Im having issues when i add the piece of code:
--This will return the top user in the list. This is to catch the application user.
SET @SQL2 = N'SELECT TOP 1 @AppUser = UpdatedBy FROM ' + @TableName
exec sp_executesql @sql2, N'@TableName NVARCHAR(50), @AppUser nvarchar(10) output',
@TableName, @AppUser output
If i take it out, it works fine and the sproc adds the trigger... maybe a single tick issue....just wanted to give someone an opportunity to try instead of giving a "messed up" sproc 🙂
CODE FOR TRIGGER:
USE [database]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Alter trigger [dbo].[Table_AuditTrigger] on [dbo].[Table] 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),
@AppUser varchar(128),
@SQL2 nvarchar(500)
-- pass the table name into the PK check variable
select @TableName = 'Table'
-- 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 = @TableName
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 = @TableName
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 = @TableName
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
-- make sure there is a key
if @PKCols is null
begin
raiserror('no PK on table %s', 16, -1, @TableName)
return
end
--This will return the top user in the list. This is to catch the application user.
SET @SQL2 = N'SELECT TOP 1 @AppUser = UpdatedBy FROM ' + @TableName
exec sp_executesql @sql2, N'@TableName NVARCHAR(50), @AppUser nvarchar(10) output',
@TableName, @AppUser output
--build the code to insert into audit table
select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName
while @field < @maxfield
begin
select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName 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, AppUser)'
select @sql = @sql + ' select ''' + @Type + ''''
select @sql = @sql + ',''' + @TableName + ''''
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 + ',''' + @AppUser + ''''
select @sql = @sql + ' from #ins i full outer join #del d'
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)'
--This is to keep the table clean...
exec (@sql)
delete from dbo.Audit where fieldname = 'UpdatedBy'
delete from dbo.Audit where fieldname = 'UpdatedDate'
end
end
GO
December 16, 2011 at 3:52 pm
What, specifically, are the 'issues' you're having? Errors? Nothing happening? Incorrect results? Something else?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 16, 2011 at 4:01 pm
I think my single ticks are not correct. I have been staring at it forever seems like and it all looks the same now... The normal "double" single ticking it doesn't seem to work.. I labled the place where I wanted to insert it .
I need to add a piece of code to the sproc i have that works.... here is the piece of code:
--This will return the top user in the list. This is to catch the application user.
SET @SQL2 = N'SELECT TOP 1 @AppUser = UpdatedBy FROM ' + @TableName
exec sp_executesql @sql2, N'@TableName NVARCHAR(50), @AppUser nvarchar(10) output',
@TableName, @AppUser output
Here is the working sproc:
USE [Database]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[spReplaceAuditTrigger]( @PassedTableName as NVarchar(255) ) AS
DECLARE @sql AS NVARCHAR(MAX)
SELECT @sql = 'create trigger [dbo].[' + @PassedTableName + '_AuditTrigger] on [dbo].['+ @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),
@SQL2 nvarchar(500),
@AppUser varchar(128)
-- pass the table name into the PK check variable
select @TableName = ' + char(39) + @PassedTableName + char(39) + '
-- 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 = @TableName
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 = @TableName
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 = @TableName
and CONSTRAINT_TYPE = ''PRIMARY KEY''
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
-- make sure there is a key
if @PKCols is null
begin
raiserror(''no PK on table %s'', 16, -1, @TableName)
return
end
-- This is where im trying to stick this new code.
--build the code to insert into audit table
select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName
while @field < @maxfield
begin
select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName 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 + '','''''' + @TableName + ''''''''
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 + '','''''' + @AppUser + ''''''''
select @sql = @sql + '' from #ins i full outer join #del d''
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)
delete from dbo.Audit where fieldname = ''UpdatedBy''
delete from dbo.Audit where fieldname = ''UpdatedDate''
end
end
'
EXEC(@SQL)
GO
December 17, 2011 at 2:12 am
robert.baird 2778 (12/16/2011)
--This will return the top user in the list. This is to catch the application user.
The problem with this is that, while the @AppName is part of the dynamic string after you build it, the @TableName is not, hence can't be in the parameter list, nor can you parameterise the table name in a query
So that should be something like
SET @SQL2 = N'SELECT TOP 1 @AppUser = UpdatedBy FROM ' + @PassedTableName -- guessing as to the right variable
exec sp_executesql @sql2, N'@AppUser nvarchar(10) output', @AppUser output
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 19, 2011 at 10:17 am
Did it help the last time we worked on this proc?
http://www.sqlservercentral.com/Forums/Topic1218042-392-1.aspx
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
December 20, 2011 at 7:08 am
Thanks Gila ...
How do I get around that? I have to be able to pass in the correct table that is being processed.. 🙁
December 20, 2011 at 7:11 am
It did, Todd, but then it affected another portion.... This piece is the last part of what I need to get the current app user from a table.... i guess the domain name is not enough... is there any other way to parameterize what i was trying to do? (what Gila above mentioned)
Dan
December 20, 2011 at 8:40 am
I'm not even sure what you're trying to do. Can you explain in more detail (just that portion, not the overall code)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 20, 2011 at 9:15 am
Sure...
On that newly created table, there are two columns. One is ModifiedBy and one is ModifiedDate (along with all the form fields). Once the table is created, which is basically form data (the table is a new form created in the app). Every time this form is updated it pulls the user name of the individual that modified the form. It pulls this from a 'users' table in the form of an int, from within the database. (Example: user=45) So from inside my sproc that writes the trigger on all these dynamically created tables (when the users create a new form), i need a way to pull the user name from the column of the new form when someone updates it. The column name is always the same: UpdatedBy. The code that you see is meant to query that table for the most recent entry (ie the person who updated last) when the trigger fires.
I hope that wan a bit clearer. I just need to be able to include some code to output the username (int) when it fires, and get it into that @AppUser variable, to be able to write the insert statement towards the bottom. The problem is the encapsulation into the dynamic query and all the tick marks i think. I can't think of another way to get that username from the users table in a way to work within the sproc.
If I take that same code, and just put it in the trigger and then test the app/table, it is successful. So, I know the code works in the trigger, its just the SPROC when creating the trigger part that doesn't like it....
Dan
December 20, 2011 at 9:17 am
Its just that part that complains. It's the piece that pulls the users name from the username table, to insert it into the query string below.
December 20, 2011 at 9:18 am
So your table has an UpdatedBy column and a ModifiedBy column, the UpdatedBy is populated by the front end and you want the trigger to copy that value from UpdatedBy to Modified By?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 20, 2011 at 9:22 am
Btw, I know this is late, but the way that trigger is written, if that's the actual trigger code that's listed in your first post, is highly inefficient. It is almost certain to cause all sorts of problems at higher usage levels. (I've seen this kind of thing a couple of times)
The proc to build a trigger is an excellent idea, but it should build a trigger that is specific for each table is is created on, not one piece of generic code that's put into a trigger on all tables
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 20, 2011 at 9:26 am
I don't disagree, but that would be a bit above what I could get up and running in a short amount of time. I'm relatively new to this kind of coding. To answer your question, yes, every table created has those two columns - ModifiedBy and ModifyDate.
December 20, 2011 at 9:27 am
The trigger will copy the value from the ModifiedBy column and place it in the AppUser variable so the query (being built towards the bottome) can insert it into the audit table.
December 20, 2011 at 9:30 am
IF EXISTS (SELECT 1 FROM #ins)
SELECT TOP 1 @AppUser = ModifiedBy FROM #ins
ELSE
SELECT TOP 1 @AppUser = ModifiedBy FROM #del
You don't need to go back to the table, you have the rows that were modified in the inserted and deleted tables, which you've written into temp tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply