January 30, 2014 at 11:13 pm
hi i need to capture column name using trigger for a table which are getting changed
For Ex:
Create table #TempTable1
(
Colum1 int,
Colum2 int,
Colum3 int,
Colum4 int,
Colum4 int
)
if i UPDATE Colum1
i need the update column name alone not the value of Colum1 i.e. Colum1
Thanks
Vijay
January 30, 2014 at 11:34 pm
You can use UPDATE() or COLUMNS_UPDATED() trigger functions to accomplish this. Refer to BOL for further details.
January 31, 2014 at 12:10 am
why u need the name ?
January 31, 2014 at 12:42 am
Thanks to all
Hi for my requirement i have to capture the Changed column Name and the Old Value alone. so to completed my task i used Cusor, Dynamic Quey and Temp table
Create table Temptable1
(
RowNoint identity(1,1),
Col1int,
Col2int,
Col3int
)
Create table Temptable1_Audit
(
ColName1Varchar(Max),
OldValueVarchar(MAx),
ChangedOnDatetime default getdate()
)
insert into Temptable1
Values
(1,2,3),
(4,5,6),
(7,8,9)
Create TRIGGER Temptable1_AfterUpate ON Temptable1
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
Select * into #deleted from deleted
Select * into #inserted from inserted
Declare @NameVarchar(Max)
Declare @SQLTmpnVarchar(Max)
Declare @RowValueVarchar(Max)= null
Declare CheckColummValue Cursor for
Selectname
fromsys.columns
whereOBJECT_ID=OBJECT_ID('Temptable1')
Open CheckColummValue
FETCH NEXT FROM CheckColummValue INTO @Name
WHILE @@FETCH_STATUS = 0
BEGIN
Set@SQLTmp='Insert into Temptable1_Audit(ColName1,OldValue)
Select'''+@Name+''',a.'+@Name+'
From#deleteda,
#insertedb
Wherea.RowNo=b.RowNo
anda.'+@Name+'<>'+'B.'+@Name
exec @RowValue =sp_executesql @SQLTmp
FETCH NEXT FROM CheckColummValue INTO @Name
End
SET NOCOUNT OFF;
END
select * from Temptable1
update Temptable1
set Col1=10
select * from Temptable1_Audit
Thanks
Vijay
January 31, 2014 at 1:05 am
this solution will be bring performance issues in case of bulk operation and if this table is heavily used you will get blocking issue. you better rethink your structure.
January 31, 2014 at 1:54 am
Yes this is for time begin solution only. As of now we are using for master table only which has, not more than 10000 rows, not for transaction table. analyzing on some other way also.
Thanks
Vijay
January 31, 2014 at 3:49 am
you can refer to the following link for the audit log techniques.
http://www.mssqltips.com/sqlservertip/1468/designing-tables-for-audit-data-in-sql-server/
hope it helps in the process.
January 31, 2014 at 8:50 am
msmvijayakumar (1/31/2014)
Thanks to allHi for my requirement i have to capture the Changed column Name and the Old Value alone. so to completed my task i used Cusor, Dynamic Quey and Temp table
Run away from this approach as absolutely fast as you can. There are so many problems with this approach it is hard to know where to begin.
Performance - Cursors in a trigger are a performance timebomb.
Dynamic sql - Your code is now vulnerable to sql injection. DO NOT use dynamic sql in a trigger.
The approach you are taking to logging is going to prove far less than helpful. You are creating an EAV style of logging table. This makes figuring out what a row looked at a given point in time nearly impossible.
You need to use a set based approach to logging. What about CDC? Have you looked into that? http://technet.microsoft.com/en-us/library/bb522489.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 5, 2014 at 5:13 am
Hi Thanks to all
For my need update will be happen very rare since due to master data.i am going with triggers.
when i try to use CDC
This change data capture process is scheduled by the SQL Server Agent that executes the capture job cdc._capture.
The capture job scans the transaction log every 5 seconds, Even i update the column are not job will run.
For Transaction Related Audit purpose CDC is better.
I have remove the Previous Concept of using Cursor, Dynamic SQL and Temp table
I have achieved the same concept using XML and Recursive function.
I my scenario bulk update will not happen update will be based on identity column so Which is far better than CDC and Previous trigger Concept performance wise.
Create TRIGGER Temptable1_AfterUpate ON Temptable1
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
Declare @XMlXMl,
@XMl2XMl
set @XMl=(select
(select *
fromdeleted
for xml path('Element') ,type
)for xml path('RootElement')
)
set @XMl2= (select
(select*
frominserted
for xml path('Element') ,type
) for xml path('RootElement')
)
;WITH OldValue AS (
SELECT
N.XMLValue.query('*')ASXMLChlid,
N.XMLValue.value('local-name(.)', 'nvarchar(128)')ASColumnName,
N.XMLValue.value('text()[1]', 'nvarchar(max)')ASColumnValue,
CAST(null as xml)ASElement
FROM@XMl.nodes('/*') AS N(XMLValue)
Union all
Select
N.XMLValue.query('*')AS XMLChlid,
N.XMLValue.value('local-name(.)', 'nvarchar(128)')AS ColumnName,
N.XMLValue.value('text()[1]', 'nvarchar(max)')AS ColumnValue,
CASE
WHEN N.XMLValue.value('fn:local-name(.)', 'varchar(100)') = 'Element'
THEN N.XMLValue.query('.')
ELSE Element END
FromOldValue RecXml
Cross Apply RecXml.XMLChlid.nodes('*') as N(XMLValue)
),
NewValue AS (
SELECT
N.XMLValue.query('*')ASXMLChlid,
N.XMLValue.value('local-name(.)', 'nvarchar(128)')ASColumnName,
N.XMLValue.value('text()[1]', 'nvarchar(max)')ASColumnValue,
CAST(null as xml)ASElement
FROM@XMl2.nodes('/*') AS N(XMLValue)
Union all
Select
N.XMLValue.query('*')AS XMLChlid,
N.XMLValue.value('local-name(.)', 'nvarchar(128)')AS ColumnName,
N.XMLValue.value('text()[1]', 'nvarchar(max)')AS ColumnValue,
CASE
WHEN N.XMLValue.value('fn:local-name(.)', 'varchar(100)') = 'Element'
THEN N.XMLValue.query('.')
ELSE Element END
FromNewValue RecXml
Cross Apply RecXml.XMLChlid.nodes('*') as N(XMLValue)
)
Insert into Temptable1_Audit(ColName1,OldValue,ChangedOn)
SELECT
OldValue.ColumnName,
OldValue.ColumnValue,
getdate()
FROMOldValue
JoinNewValueOnOldValue.Element.value('(Element/RowNo/text())[1]', 'Bigint')=NewValue.Element.value('(Element/RowNo/text())[1]', 'Bigint')
andOldValue.ColumnName=NewValue.ColumnName
WhereOldValue.ColumnValueis not null
andOldValue.ColumnValue<>NewValue.ColumnValue
SET NOCOUNT OFF;
END
February 5, 2014 at 8:08 am
msmvijayakumar (2/5/2014)
Hi Thanks to allFor my need update will be happen very rare since due to master data.i am going with triggers.
when i try to use CDC
This is an interesting approach. The biggest issue still is how can you see what a given row values were on a specific date? Once you have even a few dozen rows this becomes incredibly difficult. Ask yourself this question, "why are we auditing the changes to this table?". Think about how nearly impossible it would be to put together the history of a given row of data. Take this type of auditing and figure out what the entire row looked like on 1/10/2013.
I am glad you found a solution and thanks for sharing. I hope it works well for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply