July 12, 2010 at 9:24 am
Hi,
I need to log data into a log table when there is update to a table.
On every UPDATE to most columns done to a table myTable, it needs to be logged into time_log table from a trigger.
I am trying using cursor and i have ended up in unable to retrieve deleted/inserted values
create table time_log(
logID int identity (1,1) primary key
,taskID int
,userID varchar(16)
,logTable varchar(100)
,logColumn varchar(100)
,oldValue varchar(500)
,newValue varchar(500)
,insertedOn datetime default getdate()
,insertedBy varchar(32) default suser_sname()
,insertApplication varchar(128) default app_name()
)
CREATE TABLE [dbo].[myTable](
[user_id] [varchar](16) NOT NULL,
[user_supervisor_id] [varchar](16) NULL,
[user_name] [varchar](60) NOT NULL,
[user_abbr] [varchar](8) NOT NULL,
[rate_id] [int] NOT NULL,
[user_active] [char](1) NOT NULL DEFAULT ('Y')
) ON [PRIMARY]
GO
INSERT myTable
SELECT 1,'ASD','john',null,1,'N'
UNION ALL
SELECT 2,'DEF','miller','MI',2,'Y'
UNION ALL
SELECT 3,'FGH','San','KB',1,'Y'
GO
CREATE TRIGGER dbo.myTable_OnUpdate
ON dbo.myTable
AFTER UPDATE AS
BEGIN
SET NOCOUNT ON
declare @colName varchar(100), @sql varchar(1000),@oldValue varchar(100),@newValue varchar(100)
DECLARE @ColumnsUpdated VARBINARY(100)
SET @ColumnsUpdated = COLUMNS_UPDATED()
declare curLog cursor For
select column_name from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'myTable'
open curLog
fetch next from curLog into @colName
while @@fetch_status=0
begin
if(sys.fn_IsBitSetInBitmask
(@ColumnsUpdated,COLUMNPROPERTY(OBJECT_ID('myTable'),
@colName, 'ColumnID') ) <> 0 )
BEGIN
how to get the old value and new value here?
INSERT INTO dbo.time_log(taskID,userID,logTable,logColumn,oldValue,newValue)
SELECT NULL,i.user_id,'myTable',@colName,@oldValue,@newValue
FROM inserted i join deleted d on i.user_id=d.user_id
--is there any way to achieve like this? d.@oldValue , i.@newValue
END
fetch next from curLog into @colName
end
close curLog
deallocate curLog
END
update myTable
set
user_supervisor_id='Ann'
,user_name='Killer'
,rate_id='3'
where user_id='1'
I apologise for re-posting in T-SQL section(from general section)
Could you please help/suggest.
Thanks,
KB
Thanks,
Santhosh
July 12, 2010 at 9:35 am
on an update, the new and old records are placed into tables called INSERTED and DELETED which a trigger has access to. Are all the updates performed through stored procedures or are they performed outside the database? If in a stored proc, then you could modify it to include an OUTPUT statement that could enter the records into an Audit table.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 12, 2010 at 9:43 am
Mike01 (7/12/2010)
Are all the updates performed through stored procedures or are they performed outside the database?
Hi Mike,
updates are being done outside the database through a VC++ application for which more than 200 users have access.
So, instead of modifying the application, we thought of creating a trigger on UPDATE action.
Thanks,
KB
Thanks,
Santhosh
July 12, 2010 at 12:40 pm
Hi,
I did it....
but at the cost of creating two temporary tables inside a trigger...also with cursor....
one for INSERTED and other for DELETED...
i was forced to create temp tables so that i can use dynamic SQL...
--============
--create 2 #temp tables
--1 for inserted , one for deleted
--============
insert #myTable_i
select * from inserted
insert #myTable_d
select * from deleted
declare curLog cursor For
select column_name from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'myTable'
open curLog
fetch next from curLog into @colName
while @@fetch_status=0
begin
if(sys.fn_IsBitSetInBitmask
(@ColumnsUpdated,COLUMNPROPERTY(OBJECT_ID('myTable'),
@colName, 'ColumnID') ) <> 0 )
begin
set @sql=('
INSERT INTO dbo.time_log(taskID,userID,logTable,logColumn,oldValue,newValue)
SELECT NULL,mti.user_id,''time_users'','''+@colName+''',mtd.'+@colName+',mti.'+@colName+'
FROM #myTable_i mti join #myTable_d mtd on mti.user_id=mtd.user_id
where ISNULL(mti.'+@colName+','''')<>ISNULL(mtd.'+@colName+','''')
')
--print @sql
exec(@sql)
end
fetch next from curLog into @colName
this solves my problem...
However I'm not satisfied with the use of temp tables and cursor....
Anyone have any other approach? Please help/suggest...
Thanks,
KB
Thanks,
Santhosh
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy