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