September 18, 2010 at 9:51 am
Hi,
I am new to sql server,we have a stored procedure it creates automatic trigger for update,insert,delete operations for all tables in our database.Everytime when a record is updated in a table the description(Master Audit table Message) is stored in an audit table saying Column name:1 to 2 and the tablename ,action is also recorded in the audit table.But we need more informative messages like State:texas to california instead of StateId: 2 t0 1.I used extended properties and i wrote a stored procedure GetDisplayValuename.For example in a table extended properties i gave Audit_IDColumn = ID,Audit_ValueColumn = TypeName,Audit_Name = Blood Type,the table name is tbl_BloodType.
ALTER PROCEDURE dbo.GetDisplayValuename
@TableName SYSNAME,
@ID BIGINT,
@Result NVARCHAR(1000) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @IDColumn SQL_VARIANT
DECLARE @ValueColumn SQL_VARIANT
DECLARE @IDColumnName NVARCHAR(1000)
DECLARE @ValueColumnName NVARCHAR(1000)
DECLARE @Query NVARCHAR(1000)
DECLARE @Value NVARCHAR(1000)
DECLARE @PARAMDEF NVARCHAR(500)
DECLARE @strID NVARCHAR(1000)
SET @Query = N'SELECT @IDColumnOUT = value FROM fn_listextendedproperty(''Audit_IDColumn'', ''SCHEMA'', ''dbo'', ''TABLE'', ''' + @TableName + ''', default, default);'
SET @PARAMDEF = N'@IDColumnOUT SQL_VARIANT OUTPUT';
EXECUTE sp_executesql @Query, @PARAMDEF, @IDColumnOUT = @IDColumn OUTPUT;
SELECT @strID = CONVERT(varchar(1000), @ID)
SELECT @IDColumnName = CONVERT(NVARCHAR(1000),@IDColumn)
SET @Query = N'SELECT @ValueColumnOUT = value FROM fn_listextendedproperty(''Audit_ValueColumn'', ''SCHEMA'', ''dbo'', ''TABLE'', ''' + @TableName + ''', default, default);'
SET @PARAMDEF = N'@ValueColumnOUT SQL_VARIANT OUTPUT';
EXECUTE sp_executesql @Query, @PARAMDEF, @ValueColumnOUT = @ValueColumn OUTPUT;
SELECT @ValueColumnName = CONVERT(NVARCHAR(1000),@ValueColumn)
SET @Query = N'Select @ValueOUT = ' + @ValueColumnName + N' FROM dbo.' + quotename(@TableName) + N' WHERE ' + @IDColumnName + N'=' + @strID
SET @PARAMDEF = N'@ValueOUT NVARCHAR(1000) OUTPUT';
EXECUTE sp_executesql @Query, @PARAMDEF, @ValueOUT = @Value OUTPUT;
SELECT @Result = @Value
END
Here i declared ID as bigint,but in all tables ID maynot be bigint.So i want to write this query embedded into my trigger stored procedure .But i am not able to write this.Can anyone help me.I should make changes under form a master audit table message.In place of casting'CAST(D.' + A.COLUMN_NAME + N' AS NVARCHAR(1000)) i should check whether a FK exists for a column , if so then execute the Stored procedure dbo.GetDisplayValuename or other ways as usual the same 'CAST(D.' + A.COLUMN_NAME + N' AS NVARCHAR(1000))
--------------------------------------- form a Master Audit Table message for update -------------------------------------
SELECT @TriggerSql = @TriggerSql + N'+ CASE WHEN D.' + A.COLUMN_NAME + N' <> I.' + A.COLUMN_NAME
+ CASE WHEN A.IS_NULLABLE = N'YES' THEN N' OR (D.' + A.COLUMN_NAME + N' IS NULL AND I.' + A.COLUMN_NAME + N' IS NOT NULL) OR (D.' + A.COLUMN_NAME + N' IS NOT NULL AND I.' + A.COLUMN_NAME + N' IS NULL)' ELSE N'' END
+ N' THEN N''' + A.COLUMN_NAME
+ N': '' + ' + CASE WHEN A.IS_NULLABLE = N'YES' THEN N'ISNULL(' ELSE N'' END + N'CAST(D.' + A.COLUMN_NAME + N' AS NVARCHAR(1000))' + CASE WHEN A.IS_NULLABLE = N'YES' THEN N', N''NULL'')' ELSE N'' END
+ N' + '' [to] '' + ' + CASE WHEN A.IS_NULLABLE = N'YES' THEN N'ISNULL(' ELSE N'' END + N'CAST(I.' + A.COLUMN_NAME + N' AS NVARCHAR(1000))' + CASE WHEN A.IS_NULLABLE = N'YES' THEN N', N''NULL'')' ELSE N'' END
+ N' + '', '' ELSE N'''' END' + @NewLine
FROM INFORMATION_SCHEMA.COLUMNS A
INNER JOIN INFORMATION_SCHEMA.COLUMNS S ON S.TABLE_NAME = @TableName AND S.TABLE_SCHEMA = @TableSchemaName
AND A.COLUMN_NAME = S.COLUMN_NAME
WHERE A.TABLE_NAME = @AuditTableName AND A.TABLE_SCHEMA = @AuditTableSchemaName
-- create join of inserted and deleted tables to see the difference between them:
SET @TriggerSql = @TriggerSql + N' FROM inserted I' + @NewLine
+ N'CROSS JOIN deleted D' + @NewLine
--------------------------------INSERT TRIGGER --------------------
SET @TriggerSql = @TriggerSql + @NewLine + N'ELSE' + @NewLine
+ N'if (@Actionvariable=N''Insert'') ' + @NewLine
+ N' SELECT @AuditMessage = '
SELECT @TriggerSql = @TriggerSql + N' + N''' + A.COLUMN_NAME + N': '' + ISNULL(CAST(I.' + A.COLUMN_NAME + N' AS NVARCHAR(1000)), N''NULL'') + '', ''' + @NewLine
FROM INFORMATION_SCHEMA.COLUMNS A
INNER JOIN INFORMATION_SCHEMA.COLUMNS S ON S.TABLE_NAME = @TableName AND S.TABLE_SCHEMA = @TableSchemaName
AND A.COLUMN_NAME = S.COLUMN_NAME
WHERE A.TABLE_NAME = @AuditTableName AND A.TABLE_SCHEMA = @AuditTableSchemaName
SET @TriggerSql = @TriggerSql + N' FROM inserted I' + @NewLine
-------------------------------------------------DELETE TRIGGER ----------
SET @TriggerSql = @TriggerSql + @NewLine + N'if (@Actionvariable=N''Delete'') ' + @NewLine
+ N' SELECT @AuditMessage = '
SELECT @TriggerSql = @TriggerSql + N' + N''' + A.COLUMN_NAME + N': '' + ISNULL(CAST(D.' + A.COLUMN_NAME + N' AS NVARCHAR(1000)), N''NULL'') + '', '''-- Deleted, '''
+ @NewLine
FROM INFORMATION_SCHEMA.COLUMNS A
INNER JOIN INFORMATION_SCHEMA.COLUMNS S ON S.TABLE_NAME = @TableName AND S.TABLE_SCHEMA = @TableSchemaName
AND A.COLUMN_NAME = S.COLUMN_NAME
WHERE A.TABLE_NAME = @AuditTableName AND A.TABLE_SCHEMA = @AuditTableSchemaName
SET @TriggerSql = @TriggerSql + N' FROM Deleted D' + @NewLine
September 22, 2010 at 5:53 am
Wow. That's quite a chunk of code you've put up.
Quick question. Why do you want to use triggers? I'm not sure about how it works in SQL 2k8, but in every other version of SQL, triggers kill performance. And having a trigger on every table in the DB just strikes me as a bad idea in general.
There are other methods of auditing records if you're open to it.
September 22, 2010 at 11:53 am
CELKO (9/22/2010)
So all your tables have a magical universal column called "id"? That is not a relational database at all. This is Kabbalah magic in which all things have a magic number that God gave them.
Now that was uncalled for. This is a Newbie forum and the user did say he's new. You could try to be a little nicer.
September 22, 2010 at 9:11 pm
Hello Brandie,
You mention other ways to perform the auditing in the post that I am citing. Could you elaborate on these methodologies and provide examples?
Greatly Appreciated,
Karl
September 23, 2010 at 6:11 am
What kind of database are you auditing? OLTP? Or a data warehouse?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply