SQL parsing code in IF statement that it shouldn''t
-
All of this is part of a string that gets executed using EXEC().
(so yes, those are 2 quotes, and that's correct)
If the column "edituser" exists, i want to collect its value
if (Select Coalesce(Col_length(''inserted'',''edituser''),0)) > 0
begin
set @UserName = (select top 1 edituser from inserted)
end
I believe what's happening is that when the string is passed to the exec statement, it is checking the validity of the statement within the IF statement, and failing. It seems like I've read something about this before, but I'm having trouble finding it. Thanks in advanced for any replies
-
Is this the "inserted" table as in you're doing this in a trigger?
If so, I am not aware that EXEC will allow you to reference the inserted table: the EXEC happens outside of that context.
-
John is correct there is not direct access to the inserted table from dynamic SQL. Which you shouldn't be using anyways on a trigger
* Noel -
Yes, I am creating the trigger. So the EXEC is around the whole CREATE TRIGGER statement. I have an auditing trigger I use to audit column changes, and I'm trying to script it so that it will script out to all the user tables in the DB, otherwise I'm stuck applying the triggers manually to the tables.
-
There is nothing wrong in having a code generator for the triggers. The bad part is to use dynamic sql in the generated code!
* Noel -
Agreed. Any help with the original ? then?
-
Again, an IF statement in a CREATE TRIGGER statement is failing. The entire CREATE TRIGGER statement is being created/executed using EXEC. I believe the reason for failing is that within the IF statement a SELECT statement refers to a column that the IF statement is testing for the existance of (see original post).
-
Please post the whole code so we know what we're working with.
-
Here it is. I circumvented it by checking the table structure while creating the trigger and now it works fine. The audit trigger is one I found online @ http://www.nigelrivett.net/AuditTrailTrigger.html and modified to suit my needs. Thanks to those who helped.
declare @tables table (tablename varchar(50))
declare @table varchar(50)
declare @trigger char (5000)
insert into @tables
select table_name from information_schema.tables
where not (substring(table_name,1,3)= 'sys' or substring(table_name,1,3)= 'dtp'
or table_type = 'view')
while (select count(*) from @tables) > 0
begin
set @table = (select top 1 tablename from @tables order by tablename)
--process to be run on each table goes here
if @table 'Audit'
begin
set @trigger =
'create trigger tr_Audit_' + @table + ' on ' + @table + ' for update
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) ,
@PKSelect varchar(1000)
select @TableName = (SELECT Object_name(parent_obj) AS TableName from dbo.SysObjects WHERE id = @@procid)
-- date and user
select @UserName = system_user ,
@UpdateDate = convert(varchar(8), getdate(), 112) + '' '' + convert(varchar(12), getdate(), 114)'
if (Select Coalesce(Col_length(@table,'edituser'),0)) > 0
begin
set @trigger = rtrim(@trigger) + ' set @UserName = (select top 1 edituser from inserted)'
end
set @trigger = rtrim(@trigger) + '
-- Action
/********************************************************
This section commented out because we only use this
trigger for updates
if exists (select * from inserted)
if exists (select * from deleted)
select @Type = ''U''
else
select @Type = ''I''
else
select @Type = ''D''
Next line removed along w/comments to audit all changes*/
select @type = ''U''
-- 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
fromINFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
andCONSTRAINT_TYPE = ''PRIMARY KEY''
andc.TABLE_NAME = pk.TABLE_NAME
andc.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
-- Get primary key select for insert
select @PKSelect = coalesce(@PKSelect+''+'','''') + '''''''' + ''''''+convert(varchar(100),coalesce(i.'' + COLUMN_NAME +'',d.'' + COLUMN_NAME + ''))+''''''''''
fromINFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
andCONSTRAINT_TYPE = ''PRIMARY KEY''
andc.TABLE_NAME = pk.TABLE_NAME
andc.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
if @PKCols is null
begin
raiserror(''no PK on table %s'', 16, -1, @TableName)
return
end
select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName
while @field @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, PK, FieldName, OldValue, NewValue, UpdateDate, UserName)''
select @sql = @sql + '' select '''''' + @Type + ''''''''
select @sql = @sql + '','''''' + @TableName + ''''''''
select @sql = @sql + '','' + @PKSelect
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 + '' 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
'
print @trigger
exec(@trigger)
--
end
--end process, delete tablename from variable
delete from @tables where (tablename = @table)
end
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply