Need Help Audit trigger throwing error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need Help Audit trigger throwing error

Author

Topic

ghanshyam007
Starting Member

6 Posts

Posted - 2011-01-18 : 04:23:18

I have a trigger on a table which will update the audit table as per the function performed on the table but at time of delete it fails to update giving error on dynamic sql .
take a look on the code.
Code snippet is like this :
USE [PalladiumShare]
GO
ALTER TRIGGER Trg_WI_ProjectMapping ON WI_ProjectMapping
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),
@LastModifiedById VARCHAR(100),
@AuditDescription NVARCHAR(1000)
SELECT @TableName = 'WI_ProjectMapping'
-- Action
IF EXISTS ( SELECT *
FROM inserted )
IF EXISTS ( SELECT *
FROM deleted )
SELECT @Type = 'U'
ELSE
SELECT @Type = 'I'
ELSE
SELECT @Type = 'D'
IF @Type IN ( 'I', 'U' )
BEGIN
-- date and user
SELECT @UserName = ( SELECT TOP 1
LastModifiedByUserName
FROM Inserted
)
SELECT @LastModifiedById = ( SELECT TOP 1
CAST(LastModifiedById AS NVARCHAR(100))
FROM Inserted
)
SELECT @UpdateDate = CONVERT(VARCHAR(8), GETDATE(), 112) + ' '
+ CONVERT(VARCHAR(12), GETDATE(), 114)

END
IF @Type = 'D'
BEGIN
-- date and user
SELECT @UserName = ( SELECT TOP 1
LastModifiedByUserName
FROM Inserted
)
SELECT @LastModifiedById =11111
SELECT @UpdateDate = CONVERT(VARCHAR(8), GETDATE(), 112) + ' '
+ CONVERT(VARCHAR(12), GETDATE(), 114)

END
-- get list of columns
SELECT *
INTO #ins
FROM inserted
SELECT *
INTO #del
FROM deleted
-- Get primary key columns for full outer join
SET @PKCols = NULL
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
IF @PKCols IS NULL
BEGIN
RAISERROR ( 'no PK on table %s', 16, - 1, @TableName )
RETURN
END
--if value is inserted then insert one record in audit table
IF @type = 'I'
BEGIN
DECLARE @PKColumn NVARCHAR(255)
SELECT @PKColumn = COALESCE(@PKColumn + ' ,', ' ')
+ +c.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE pk.TABLE_NAME = 'WI_ProjectMapping'
AND CONSTRAINT_TYPE = 'PRIMARY KEY'
AND c.TABLE_NAME = pk.TABLE_NAME
AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

INSERT INTO WI_Audit
(
Type,
TableName,
PrimaryKeyField,
PrimaryKeyValue,
FieldName,
OldValue,
NewValue,
LastModifiedById,
LastModifiedDateTime,
LastModifiedByUserName,
AuditDescription
)
SELECT 'I',
@TableName,
@PKColumn,
CONVERT(NVARCHAR(255), Projectid) + ' , '
+ fieldName + ' , '
+ CONVERT(NVARCHAR(255), FieldValue),
'CreatedBy',
NULL,
CreatedById,
LastModifiedById,
GETDATE(),
LastModifiedByUserName,
'Record Inserted For :' + @TableName
FROM inserted
RETURN
END
SELECT @field = 0,
@maxfield = MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
SET @AuditDescription = 'Record Updated in ' + @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 WI_Audit (Type, TableName, PrimaryKeyField, PrimaryKeyValue, FieldName, OldValue, NewValue, LastModifiedById, LastModifiedDateTime, LastModifiedByUserName,AuditDescription)'
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 + ',' + ' CAST(' + @LastModifiedById
+ ' as bigint) ' + ''
SELECT @sql = @sql + ',''' + @UpdateDate + ''''
SELECT @sql = @sql + ',''' + @UserName + ''''
SELECT @sql = @sql + ',''' + @AuditDescription
+ ' For Column ' + @fieldName + ''''
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)'
SELECT @sql
EXEC ( @sql
)
END
END

when a procedure is trying to delete a record from WI_ProjectMapping table the trigger is througin error
because that time the dynamic sql got failed the result generated by the dynamicsql after executing the trigger is :
insert WI_Audit
(Type,
TableName,
PrimaryKeyField,
PrimaryKeyValue,
FieldName,
OldValue,
NewValue,
LastModifiedById,
LastModifiedDateTime,
LastModifiedByUserName,
AuditDescription)
select 'D',
'WI_ProjectMapping',
+'FieldName'+'FieldValue'+'ProjectId',
+convert(varchar(100),
coalesce(i.FieldName,d.FieldName))+convert(varchar(100),
coalesce(i.FieldValue,d.FieldValue))+convert(varchar(100),
coalesce(i.ProjectId,d.ProjectId)),
'ProjectId',
convert(varchar(1000),d.ProjectId),
convert(varchar(1000),i.ProjectId),
CAST(11111 as bigint) ,
'20110118 14:28:52:363','',
'Record Updated in WI_ProjectMapping For Column ProjectId'
from #ins i full outer join #del d
and i.FieldName = d.FieldName AND i.FieldValue = d.FieldValue and i.ProjectId = d.ProjectId
where i.ProjectId <> d.ProjectId
or (i.ProjectId is null and d.ProjectId is not null)
or (i.ProjectId is not null and d.ProjectId is null)

as you can see the bold text which is generate by the coalesce function which should be generated like this
on i.FieldName = d.FieldName AND i.FieldValue = d.FieldValue and i.ProjectId = d.ProjectId

thats where the trigger is failing.
any help will be highly appreciated from your side.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-01-18 : 04:35:19

Can you please edit your post and add [ code ] and [/ code ] tags around your code?

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

ghanshyam007
Starting Member

6 Posts

Posted - 2011-01-18 : 05:06:53

@ Lumbago:
I have a trigger on a table which will update the audit table as per the function performed on the table but at time of delete it fails to update giving error on dynamic sql .
take a look on the code.


USE [PalladiumShare]
GO
ALTER TRIGGER Trg_WI_ProjectMapping ON WI_ProjectMapping
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),
@LastModifiedById VARCHAR(100),
@AuditDescription NVARCHAR(1000)
SELECT @TableName = 'WI_ProjectMapping'
-- Action
IF EXISTS ( SELECT *
FROM inserted )
IF EXISTS ( SELECT *
FROM deleted )
SELECT @Type = 'U'
ELSE
SELECT @Type = 'I'
ELSE
SELECT @Type = 'D'
IF @Type IN ( 'I', 'U' )
BEGIN
-- date and user
SELECT @UserName = ( SELECT TOP 1
LastModifiedByUserName
FROM Inserted
)
SELECT @LastModifiedById = ( SELECT TOP 1
CAST(LastModifiedById AS NVARCHAR(100))
FROM Inserted
)
SELECT @UpdateDate = CONVERT(VARCHAR(8), GETDATE(), 112) + ' '
+ CONVERT(VARCHAR(12), GETDATE(), 114)

END
IF @Type = 'D'
BEGIN
-- date and user
SELECT @UserName = ( SELECT TOP 1
LastModifiedByUserName
FROM Inserted
)
SELECT @LastModifiedById =11111
SELECT @UpdateDate = CONVERT(VARCHAR(8), GETDATE(), 112) + ' '
+ CONVERT(VARCHAR(12), GETDATE(), 114)

END
-- get list of columns
SELECT *
INTO #ins
FROM inserted
SELECT *
INTO #del
FROM deleted
-- Get primary key columns for full outer join
SET @PKCols = NULL
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
IF @PKCols IS NULL
BEGIN
RAISERROR ( 'no PK on table %s', 16, - 1, @TableName )
RETURN
END
--if value is inserted then insert one record in audit table
IF @type = 'I'
BEGIN
DECLARE @PKColumn NVARCHAR(255)
SELECT @PKColumn = COALESCE(@PKColumn + ' ,', ' ')
+ +c.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE pk.TABLE_NAME = 'WI_ProjectMapping'
AND CONSTRAINT_TYPE = 'PRIMARY KEY'
AND c.TABLE_NAME = pk.TABLE_NAME
AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

INSERT INTO WI_Audit
(
Type,
TableName,
PrimaryKeyField,
PrimaryKeyValue,
FieldName,
OldValue,
NewValue,
LastModifiedById,
LastModifiedDateTime,
LastModifiedByUserName,
AuditDescription
)
SELECT 'I',
@TableName,
@PKColumn,
CONVERT(NVARCHAR(255), Projectid) + ' , '
+ fieldName + ' , '
+ CONVERT(NVARCHAR(255), FieldValue),
'CreatedBy',
NULL,
CreatedById,
LastModifiedById,
GETDATE(),
LastModifiedByUserName,
'Record Inserted For :' + @TableName
FROM inserted
RETURN
END
SELECT @field = 0,
@maxfield = MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
SET @AuditDescription = 'Record Updated in ' + @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 WI_Audit (Type, TableName, PrimaryKeyField, PrimaryKeyValue, FieldName, OldValue, NewValue, LastModifiedById, LastModifiedDateTime, LastModifiedByUserName,AuditDescription)'
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 + ',' + ' CAST(' + @LastModifiedById
+ ' as bigint) ' + ''
SELECT @sql = @sql + ',''' + @UpdateDate + ''''
SELECT @sql = @sql + ',''' + @UserName + ''''
SELECT @sql = @sql + ',''' + @AuditDescription
+ ' For Column ' + @fieldName + ''''
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)'
SELECT @sql
EXEC ( @sql
)
END
END

when a procedure is trying to delete a record from WI_ProjectMapping table the trigger is througin error
because that time the dynamic sql got failed the result generated by the dynamicsql after executing the trigger is :


insert WI_Audit
(Type,
TableName,
PrimaryKeyField,
PrimaryKeyValue,
FieldName,
OldValue,
NewValue,
LastModifiedById,
LastModifiedDateTime,
LastModifiedByUserName,
AuditDescription)
select 'D',
'WI_ProjectMapping',
+'FieldName'+'FieldValue'+'ProjectId',
+convert(varchar(100),
coalesce(i.FieldName,d.FieldName))+convert(varchar(100),
coalesce(i.FieldValue,d.FieldValue))+convert(varchar(100),
coalesce(i.ProjectId,d.ProjectId)),
'ProjectId',
convert(varchar(1000),d.ProjectId),
convert(varchar(1000),i.ProjectId),
CAST(11111 as bigint) ,
'20110118 14:28:52:363','',
'Record Updated in WI_ProjectMapping For Column ProjectId'
from #ins i full outer join #del d
and i.FieldName = d.FieldName AND i.FieldValue = d.FieldValue and i.ProjectId = d.ProjectId
where i.ProjectId <> d.ProjectId
or (i.ProjectId is null and d.ProjectId is not null)
or (i.ProjectId is not null and d.ProjectId is null)

as you can see the bold text which is generate by the coalesce function which should be generated like this
on i.FieldName = d.FieldName AND i.FieldValue = d.FieldValue and i.ProjectId = d.ProjectId

thats where the trigger is failing.
any help will be highly appreciated from your side.

ghanshyam007
Starting Member

6 Posts

Posted - 2011-01-18 : 05:44:05

Finall i resolved the issue, by hardcoding the @PKValues in the trigger
[Code] SELECT @sql = @sql
+ ' from #ins i full outer join #del d on i.FieldName = d.FieldName and i.FieldValue = d.FieldValue and i.ProjectId = d.ProjectId '
[/Code]

although this is not the correct way to do but when you are in fly u need to do dirtyfix like this,

if anyone can figure the issue do post ur sugession.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources