June 14, 2013 at 11:17 pm
In want to find which columns are updated in a row of a table, and what is their new value and previous value.
i know a trigger can work here, with use of columns_updated(). but i want an exact T-SQL script, and any other alternatives for this process.
Thanks a Ton in advance. 🙂
June 14, 2013 at 11:41 pm
any sql master for help.?
June 15, 2013 at 1:10 am
any answer for this question please.?
June 15, 2013 at 7:14 am
June 15, 2013 at 7:48 am
Mr. Kapsicum (6/14/2013)
In want to find which columns are updated in a row of a table, and what is their new value and previous value.i know a trigger can work here, with use of columns_updated(). but i want an exact T-SQL script, and any other alternatives for this process.
Thanks a Ton in advance. 🙂
Here is my problem, I have no idea how you want this information captured. There is really nothing here with which to work with in order to provide you with an answer.
It would help if you posted the DDL (CREATE TABLE statement) for the table you are using, sample data (as a series of INSERT INTO statements or using the row constructor style since this is posted in a SQL Server 2008 forum), addition sample data to be used to update the table and test the capture of information, and then expected results based on the sample data sets.
Before posting code be sure to test it in an empty database to be sure it all works as expected.
Please remember we are volunteers on this site and the more information you provide regarding what you are trying to do the better answers you will get in return.
June 15, 2013 at 8:22 am
Mr. Kapsicum (6/14/2013)
In want to find which columns are updated in a row of a table, and what is their new value and previous value.i know a trigger can work here, with use of columns_updated(). but i want an exact T-SQL script, and any other alternatives for this process.
Thanks a Ton in advance. 🙂
OK...
below is some test code that you can play with...in the past I have used this mainly for auditing master / standing details tables (like products/customers etc)......for table where updates are infrequent it works quite well.
The original code is not my own and IIRC I have made a few alterations, I cannot recall where originally found.
basic overview is to create a table called "Audit"....this will be used to store the details.
a trigger for each table that requires audit must exist.
the code below is set for UPDATES only....if you want INSERT/DELETE amend the code appropriately as per comments in code.
please be aware of performance slow down .... a new row is written for each table column affected...this can kill performance.
anyways....please try this and let us all know how you get on
use [tempdb]
GO
IF OBJECT_ID('tempdb..TransData', 'U') IS NOT NULL
DROP TABLE tempdb..TransData ;
IF OBJECT_ID('tempdb..Audit', 'U') IS NOT NULL
DROP TABLE tempdb..Audit ;
-- create the audit table.
-- There will only need to be one of these in a database...can be updated from any table with relevant table trigger
CREATE TABLE Audit
(Type CHAR(1),
TableName VARCHAR(128),
PK VARCHAR(1000),
FieldName VARCHAR(128),
OldValue VARCHAR(1000),
NewValue VARCHAR(1000),
UpdateDate datetime,
UserName VARCHAR(128))
GO
--crate some test data
SELECT TOP 100000
TranID = IDENTITY(INT, 1, 1),
CustomerID = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
Trans_type = CHAR(Abs(Checksum(Newid())) % 2 + 65)
+ CHAR(Abs(Checksum(Newid())) % 3 + 65)
+ CHAR(Abs(Checksum(Newid())) % 7 + 65),
ProdID = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
Sales_Amount= CAST(Rand(Checksum(Newid())) * 99 + 1 AS DECIMAL(5, 2)),
Trans_Date = Dateadd(dd, Abs(Checksum(Newid()))%Datediff(dd, '2011', '2012'), '2011')
INTO TransData
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
CROSS JOIN sys.all_columns ac3
--note that for this system to work there must be a primary key to the table
ALTER TABLE [dbo].[TransData] ADD CONSTRAINT [PK_TransData] PRIMARY KEY CLUSTERED ([TranID] ASC)
GO
--create the trigger . This has to be created for every table you want to record changes in table "Audit"
CREATE TRIGGER [dbo].[transdata_Audit]
ON [dbo].[TransData]
FOR
--INSERT, ---uncomment if required
--DELETE, ---uncomment if required
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)
, @PKField VARCHAR(1000)
----=========You will need to change @TableName to match the table to be audited=========--------
SELECT @TableName = 'TransData'
-- get date and user
SELECT @UserName = SYSTEM_USER
, @UpdateDate = CONVERT(VARCHAR(8), GETDATE(), 112) + ' ' + CONVERT(VARCHAR(12), GETDATE(), 114)
-- 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
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 to post to audittable
SELECT @PKField = COALESCE(@PKField + '+', '') + ''''
+ '''+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
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,
PK,
FieldName,
OldValue,
NewValue,
UpdateDate,
UserName)
select ''' + @Type + ''',''' + @TableName + ''',' + @PKField + ','''
+ @fieldname + '''' + ',convert(varchar(1000),d.'
+ @fieldname + ')' + ',convert(varchar(1000),i.'
+ @fieldname + ')' + ',''' + @UpdateDate + ''''
+ ',''' + @UserName + ''''
+ ' from #ins i full outer join #del d' + @PKCols
+ ' where i.' + @fieldname + ' <> d.' + @fieldname
+ ' or (i.' + @fieldname + ' is null and d.'
+ @fieldname + ' is not null)' + ' or (i.'
+ @fieldname + ' is not null and d.' + @fieldname
+ ' is null)'
EXEC ( @sql )
END
END
GO
-------------------------------------------------------
-- do some updates
-------------------------------------------------------
UPDATE TransData
SET Trans_type = 'JLS',
Sales_Amount = Sales_Amount * 1.2
WHERE (TranID % 10000 = 0)
SELECT * FROM Audit;
--TRUNCATE TABLE Audit;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply