July 23, 2003 at 12:57 am
Dear all,
My boss asked me to monitor changes in some tables: who changed what columns with which value. Triggers will do that trick, but instead of writing code for each column like
IF UPDATE(col1)...
IF UPDATE(col2)... etc
is it possible to determine the name of the column? I couldn't find any help anywhere.
Thanks for any input. Gerry.
July 23, 2003 at 1:13 am
If you use the value of the trigger's bitmask variable (function) COLUMNS_UPDATED(), then you should be able to analyse that bitstring, joining with syscolumns or information schema views to determine the column name.
Cheers,
- Mark
July 23, 2003 at 1:14 am
If you use the value of the trigger's bitmask variable (function) COLUMNS_UPDATED(), then you should be able to analyse that bitstring, joining with syscolumns or information schema views to determine the column name.
Cheers,
- Mark
July 23, 2003 at 1:34 am
Thank you for your reply.
Apparently it can be done, but could you give a simple example? I don't kow how to use the bitmask variables.
Gerry.
July 23, 2003 at 2:52 am
I'm not real big on bitmasks myself, so here's something I've hacked together. Undoubtedly someone could do better, but it's a start.
DROP TABLE MyAudit
GO
CREATE TABLE MyAudit (
id BIGINT IDENTITY PRIMARY KEY,
Updated_Table SYSNAME,
Updated_Column SYSNAME,
Updater SYSNAME,
Update_Date DATETIME
)
GO
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'trig_test'
AND type = 'TR')
DROP TRIGGER trig_test
GO
CREATE TRIGGER trig_test
ON tempdb.dbo.MyTable
FOR INSERT, UPDATE
AS
SET NOCOUNT ON
DECLARE @ColsUpdated BIGINT , @Colid INT
SET @Colid = 0
SELECT @ColsUpdated = CONVERT(BIGINT, COLUMNS_UPDATED())
WHILE @ColsUpdated <> 0 BEGIN
SET @Colid = @Colid + 1
IF @ColsUpdated % 2 = 1
INSERT MyAudit (Updated_Table, Updated_Column, Updater, Update_Date)
SELECT 'MyTable', COLUMN_NAME, CURRENT_USER, GETDATE()
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTable' AND TABLE_SCHEMA = 'dbo' AND ORDINAL_POSITION = @Colid
SET @ColsUpdated = @ColsUpdated / 2
END
GO
Cheers,
- Mark
July 23, 2003 at 3:37 am
Hi gserdijn,
use tempdb;
go
create table t (
Col01 int , Col02 int , Col03 int , Col04 int ,
Col05 int , Col06 int , Col07 int , Col08 int ,
Col09 int , Col10 int , Col11 int , Col12 int ,
Col13 int , Col14 int , Col15 int , Col16 int ,
Col17 int
);
insert into t values (
1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11 , 12 , 13 , 14 , 15 , 16 , 17
);
go
create trigger ut on t for update
as
-- General formula:
/*
Each byte of columns_updated() encodes values for 8 columns.
So byte #1 contains status of 1 - 8 columns , byte #2 contains
status of 9 - 16 columns & so on.
The general formula for calculation is:
set @byte = ( ( @Col - 1 ) / 8 ) + 1
set @exp = ( @Col - 1 ) % 8
-- for single column update, use:
if substring( columns_updated() , @byte , 1 ) & power( 2, @exp ) > 0
-- for multiple column updates , use:
if substring( columns_updated() , @byte , 1 ) & @power = @power
*/
declare @Col smallint , @exp smallint , @byte smallint , @power int
select @Col = 4 , @exp = ( @Col - 1 ) % 8,
@byte = ( ( @Col - 1 ) / 8 ) + 1
if substring( columns_updated() , @byte , 1 ) & power( 2, @exp ) > 0
print 'Column #4 was updated'
select @Col = 9 , @exp = ( @Col - 1 ) % 8,
@byte = ( ( @Col - 1 ) / 8 ) + 1
if substring( columns_updated() , @byte , 1 ) & power( 2, @exp ) > 0
print 'Column #9 was updated'
select @Col = 15 , @exp = ( @Col - 1 ) % 8,
@byte = ( ( @Col - 1 ) / 8 ) + 1
if substring( columns_updated() , @byte , 1 ) & power( 2, @exp ) > 0
print 'Column #15 was updated'
select @Col = 16 , @exp = ( @Col - 1 ) % 8,
@byte = ( ( @Col - 1 ) / 8 ) + 1
if substring( columns_updated() , @byte , 1 ) & power( 2, @exp ) > 0
print 'Column #16 was updated'
select @Col = 17 , @exp = ( @Col - 1 ) % 8,
@byte = ( ( @Col - 1 ) / 8 ) + 1
if substring( columns_updated() , @byte , 1 ) & power( 2, @exp ) > 0
print 'Column #17 was updated'
select @Col = 11 , @exp = ( @Col - 1 ) % 8,
@byte = ( ( @Col - 1 ) / 8 ) + 1 ,
@power = power( 2 , @exp )
select @Col = 13 , @exp = ( @Col - 1 ) % 8,
@power = @power + power( 2 , @exp )
if substring( columns_updated() , @byte , 1 ) & @power = @power
print 'Column #11 & #13 was updated'
go
-- Here are some update statements to check the trigger logic with:
-- The PRINT statement output from the trigger is shown below the
-- update statement that modifies the column(s) that we check for.
update t set col04 = col04 * 2;
/*
Column #4 was updated
*/
update t set col08 = col08 * 2;
update t set col09 = col09 * 2;
/*
Column #9 was updated
*/
update t set col12 = col12 * 2;
update t set col15 = col15 * 2;
/*
Column #15 was updated
*/
update t set col16 = col16 * 2;
/*
Column #16 was updated
*/
update t set col17 = col17 * 2;
/*
Column #17 was updated
*/
update t set col11 = col11 * 2;
update t set col11 = col11 * 2 , col13 = col13 * 2;
/*
Column #11 & #13 was updated
*/
go
drop table t;
go
wish I was the author, but in fact it was created by SQL Server MVP Umachandar Jayachandran
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
July 23, 2003 at 4:46 am
Thank you both for your response.
The script of mccork combined with the explanation of teh general formula by a5xo3z1 has helped me a lot!
Gerry.
July 24, 2003 at 4:25 pm
This method checks each column for updates
/* Set the Table Name */
Set @TableName = 'MyTable' /* Be sure to change this!!! */
/* Initialize the Column count and total number of columns */
Select @ColID =0, @ColCnt =(Select Count(*) From Information_Schema.Columns
Where Table_Name = @TableName
and Table_Schema = 'dbo')
/* Process each column until column count > number of columns */
While @ColID <= @ColCnt Begin
Set @ColId = @ColId +1/* increment the column counter */
/* Calculate the Variable for this iteration*/
Set @Updt = SUBSTRING(COLUMNS_UPDATED(), (@ColID-1) / 8 +1,1)/* Columns within 'Byte' being updated */
Set @Mask = power(2, ((@ColID -1) % 8))/* Value for Column being compared */
Set @Result = @Updt & @Mask/* Results of Bitwise 'And' on column and Byte */
/* Check the result and insert the audit record of @Result is > Zero */
IF @Result > 0
Insert TransAudit (TableName, ColumnName, UserName,DateTimeStamp,DataType,Ordinal_Position)
Select @TableName, Column_Name, Current_User, GetDate(), Data_Type, @ColID
From Information_Schema.Columns
Where Table_Name = @TableName
and Table_Schema = 'dbo'
and Ordinal_position = @ColID
end
David Kaldenberg
david.e.kaldenberg1@jsc.nasa.gov
David Kaldenberg
david.e.kaldenberg1@jsc.nasa.gov
July 28, 2003 at 1:49 am
Gentlemen, thanks again for some slick T-SQL.
So I can determine the column, but now I need to determine the value.
I can use something like:
SELECT @sql = 'SELECT ' & @ColName & ' FROM ' & @TableName & ' WHERE ' &
@TableName & '_id = ' & CAST(@Id AS VARCHAR)
EXEC (@sql)
(I use the & for the add sign, because otherwise I couldn't see it in the preview)
It works, but I wonder if there is a nicer, better way to achieve this.
Thanks for any input, Gerry
July 28, 2003 at 1:52 am
Oops, forgot to mention that I determine @Id by means of the INSERTED table.
SELECT @Id = MyTableId FROM INSERTED
Gerry
July 28, 2003 at 3:15 am
Hi Gerry,
quote:
So I can determine the column, but now I need to determine the value...
It works, but I wonder if there is a nicer, better way to achieve this.
well, no solution, but some general thoughts on this.
I audit changes on a few tables with VERY sensitve data, too. But I've made a 1:1 duplicate table for this and added two fields who changed (DEFAULT SUSER_SNAME() and when data changed (DEFAULT GETDATE()). I was also playing around trying to determine what column changed, pretty similar to what you want to do now.
And I think, unless there are no really good reasons for, the cost (meaning your development time) trying to determine what column changed outweight the gain by far. In addition, the data is more readable to me, when I see the whole row at once and not only the changed value.
Are the tables you're doing this for, frequently updated and heavy used?
Cheers,
Frank
Edited by - a5xo3z1 on 07/28/2003 03:16:27 AM
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
July 28, 2003 at 3:22 am
Forgot to post the example. This might also give you the idea how to get the old and new value from deleted and inserted tables
CREATE TRIGGER updKapitalanlagenummern
ON dbo.tblKapitalanlagenummern
FOR update AS
--IF (COLUMNS_UPDATED() & 14) >0
BEGIN
INSERT INTO auditKapitalanlagenummern
(audit_log_type,
audit_KapitalanlagenID,
audit_KaNr,
audit_WKN,
audit_Kapitalanlagenbezeichnung,
audit_RisikoklasseID,
audit_Emission,
audit_Fälligkeit,
audit_CiL,
audit_CiV,
audit_PbL,
audit_PbV,
audit_StrukturiertesProdukt,
audit_Derivate,
audit_RIC,
audit_RatingID,
audit_EmittentID,
audit_ErstelltAm,
audit_ErstelltVonID,
audit_GeändertAm,
audit_GeändertVonID,
audit_DeletedON,
audit_DeletedBy,
audit_Deleted,
audit_FRVFonds,
audit_isin_nr,
audit_in_index,
audit_is_sust)
SELECT 'OLD',
del.KapitalanlagenID,
del.[Ka-Nr],
del.WKN,
del.Kapitalanlagenbezeichnung,
del.RisikoklasseID,
del.Emission,
del.Fälligkeit,
del.CiL,
del.CiV,
del.PbL,
del.PbV,
del.StrukturiertesProdukt,
del.Derivate,
del.RIC,
del.RatingID,
del.EmittentID,
del.ErstelltAm,
del.ErstelltVonID,
del.GeändertAm,
del.GeändertVonID,
del.DeletedON,
del.DeletedBy,
del.Deleted,
del.FRVFonds,
del.isin_nr,
del.in_index,
del.is_sust
FROM deleted del
INSERT INTO auditKapitalanlagenummern
(audit_log_type,
audit_KapitalanlagenID,
audit_KaNr,
audit_WKN,
audit_Kapitalanlagenbezeichnung,
audit_RisikoklasseID,
audit_Emission,
audit_Fälligkeit,
audit_CiL,
audit_CiV,
audit_PbL,
audit_PbV,
audit_StrukturiertesProdukt,
audit_Derivate,
audit_RIC,
audit_RatingID,
audit_EmittentID,
audit_ErstelltAm,
audit_ErstelltVonID,
audit_GeändertAm,
audit_GeändertVonID,
audit_DeletedON,
audit_DeletedBy,
audit_Deleted,
audit_FRVFonds,
audit_isin_nr,
audit_in_index,
audit_is_sust)
SELECT 'NEW',
ins.KapitalanlagenID,
ins.[Ka-Nr],
ins.WKN,
ins.Kapitalanlagenbezeichnung,
ins.RisikoklasseID,
ins.Emission,
ins.Fälligkeit,
ins.CiL,
ins.CiV,
ins.PbL,
ins.PbV,
ins.StrukturiertesProdukt,
ins.Derivate,
ins.RIC,
ins.RatingID,
ins.EmittentID,
ins.ErstelltAm,
ins.ErstelltVonID,
ins.GeändertAm,
ins.GeändertVonID,
ins.DeletedON,
ins.DeletedBy,
ins.Deleted,
ins.FRVFonds,
ins.isin_nr,
ins.in_index,
ins.is_sust
FROM inserted ins
END
HTH
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply