July 24, 2008 at 3:49 am
Good day ALL
Below is a script I created to add auditing to all my tables. (More than 100)
The only problem is that the trigger that I create updates ALL the rows in the table.
Is there a way that the trigger can only update the “Updated” record without specifying a particular field in the table?
Declare @TableName nvarchar(100)
Declare @SQL nvarchar(1000)
declare tables_cursor CURSOR FOR
select [name] from sysobjects
where xtype = 'U'
and [name] not like 'sys%'
order by [name]
Open tables_cursor
fetch next from tables_cursor into @TableName
WHILE @@FETCH_STATUS = 0
Begin
--Add audit table
set @SQL = 'ALTER TABLE '+@TableName+' ADD audDateLastChanged datetime DEFAULT getdate()'
exec (@SQL)
print @SQL
--update current records
set @SQL = 'update '+@TableName+' set audDateLastChanged = getdate()'
exec (@SQL)
print @SQL
--create trigger for updates on each table
set @SQL = 'create trigger trg_u_'+@TableName+'_audDatelastChanged '
set @SQL = @SQL + ' on '+@TableName
set @SQL = @SQL + ' for update '
set @SQL = @SQL + ' as '
set @SQL = @SQL + ' begin '
set @SQL = @SQL + ' update '+@TableName
set @SQL = @SQL + ' set audDateLastChanged = getdate()'
set @SQL = @SQL + ' from inserted'
set @SQL = @SQL + ' end'
exec (@SQL)
print @SQL
fetch next from tables_cursor into @TableName
end
CLOSE tables_cursor
DEALLOCATE tables_cursor
July 24, 2008 at 7:59 am
You'll have to add the primary key to a Where clause in the trigger.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply