November 25, 2015 at 3:18 am
hello team,
using below trigger to maintain the audit history for table, newly added old and new values in the table.
how to get those values updated using this trigger - fields are highlighted.
-------------
alter trigger [dbo].changetrack
on [dbo].ch
after update
as begin
set nocount on;
declare @idtable int
select @idtable = t.id from sysobjects p join sysobjects t on p.parent_obj = t.id where p.id = @@procid
declare @columns_updated varchar(50)
select @columns_updated = isnull(@columns_updated + ', ', '') + name
from syscolumns
where id = @idtable
and convert(varbinary,reverse(columns_updated())) & power(convert(bigint, 2), colorder - 1) > 0
if update (val1) or update(val2)
begin
insert into ch_track (id,action,field_name, datetime_1,user_name,old_val, new_val)
select ch.id,'u',@columns_updated,getdate(),'lcc001' from ch ch
inner join inserted i on ch.id=i.id
end
end
November 25, 2015 at 3:28 am
It's not clear what your requirement is, but since up UPDATE statement is effectively a DELETE followed by an INSERT, you'll need to query both the Inserted and Deleted virtual tables in your trigger.
John
November 25, 2015 at 3:30 am
Based on the operation you can choose what to do. Inserted keeps always the new values, whereas old values are kept in deleted.
Declare @operation char(1) SET @operation= 'N'
IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted) SET @operation = 'U' --update
IF EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted) SET @operation = 'I' --insert
IF NOT EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted) SET @operation = 'D' --delete
Igor Micev,My blog: www.igormicev.com
November 25, 2015 at 3:31 am
without "old_val, new_val" trigger is working fine,want to add the old and new values to know which value is udpated to what.
please help in query.
November 25, 2015 at 3:40 am
Minnu (11/25/2015)
without "old_val, new_val" trigger is working fine,want to add the old and new values to know which value is udpated to what.please help in query.
Maybe this code could solve your problem
if update (val1) or update(val2)
begin
insert into ch_track (id,action,field_name, datetime_1,user_name,old_val, new_val)
select ch.id,'u',@columns_updated,getdate(),'lcc001' from ch_track ch
inner join inserted i on ch.id=i.id
update ch_track
set old_val = d.old_val
from ch_track ch
inner join deleted d on ch.id=d.id
end
Igor Micev,My blog: www.igormicev.com
November 25, 2015 at 3:57 am
You're doing this the wrong way. First, stop using sysobjects, which is provided for backwards compatibility with SQL Server 2000 only. Second, why do you need to use sysobjects to get the table ID anyway - you know at design time what table it is since it's the table you created the trigger on. Third, don't use an EAV (entity attribute value) design for your audit table. Set it up instead so that it has the same columns as the base table (possibly repeated twice for old and new, plus a few extra for who, when and where.
John
November 25, 2015 at 7:31 am
John Mitchell-245523 (11/25/2015)
You're doing this the wrong way. First, stop using sysobjects, which is provided for backwards compatibility with SQL Server 2000 only. Second, why do you need to use sysobjects to get the table ID anyway - you know at design time what table it is since it's the table you created the trigger on. Third, don't use an EAV (entity attribute value) design for your audit table. Set it up instead so that it has the same columns as the base table (possibly repeated twice for old and new, plus a few extra for who, when and where.John
It's even worse than EAV here. They are sticking all the column names from the table into a single comma delimited list. Then I assume they are wanting to do the same to the values. I couldn't agree more that this is completely the wrong to go about this.
OP -
This is creating a very painful process to log your data. Next consider that to use this data you will be forced to unravel the mess you are shoving into the table. It doesn't save any space but it will cost you performance. Also, there really is no need to track the old AND new values every time. You are doubling the amount of logging data doing this because the next row by date will have the other part of the information.
If you really want help making a better audit of your table please post the create table statement for your base table.
_______________________________________________________________
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 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply