November 2, 2009 at 4:35 am
Triggers not always working.
I have a table, which is accessed via a View. The view has 1 Instead of trigger and the table has 1 Update Trigger to populate an Audit Table.
Both the triggers work fine, if an administrator does an update. However, for a normal user, the Instead of Trigger on View fires, but the Audit trigger on table does not fire. I have even given the user access to insert rows in the Audit table.
I am not sure where I am doing something wrong.
If anyone has any ideas on what could be wrong or suggestions on what to check or change, then Kindly advise.
Thanks in advance.
The audit trigger code is as follows:
create trigger [Tr_Foreign_Currency_Grants_Data_Update]
on [dbo].[Foreign_Currency_Grants_Data]
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 = 'Foreign_Currency_Grants_Data'
-- Get date and user
select @UserName = system_user ,
@UpdateDate = convert(varchar(8), getdate(), 112) + ' ' + convert(varchar(12), getdate(), 114)
-- Record 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
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+'+','') + '''<' + COLUMN_NAME + '=''+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
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, PrimaryKey, 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
November 2, 2009 at 5:38 am
Are any errors reported ?
try using executing the script after using SETUSER to move your session to someone with non dba permissions
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply