数据库ABC 里的所有用户表(不包括后面说的TMPDB表)如果发生了记录的变化,如:插入,更新,删除时,就向表TMPDB插入对应的数据,TMPDB表字段如下:
TblName(表名),States(标识,0=插入,1=更新,2=删除) ,Indate(日期,自动的getdate)
TblName(表名),States(标识,0=插入,1=更新,2=删除) ,Indate(日期,自动的getdate)
调试欢乐多
for insert,update,delete
as
if @@rowcount = 0
return
declare @States int
if exists(select * from inserted)
if exists(select * from deleted)
set @States=1
else
set @States=0
else
set @States = 2
insert tempdb(TblName, States)
values('表名', @States)
go
从而对每个用户表分别写inset, delete, update触发器吧
但是这样看起来不是很好, 如果有N张表,就会生成3*N个触发器了。我建议,你还是再看看,你的需求是否合理吧。
或是用审计吧
declare hcforeach cursor global
for
select cast(N'
create trigger '
+cast(quotename('tr_record_'+name) as varchar(8000))
+' on '+cast(quotename(user_name(uid)) as varchar(8000))
+'.'+cast(quotename(name) as varchar(8000))+'
for insert,update,delete
as
if @@rowcount = 0
return
declare @States int
if exists(select * from inserted)
if exists(select * from deleted)
set @States=1
else
set @States=0
else
set @States = 2
insert tempdb(TblName, States)
values('+cast(quotename(name,'''') as varchar(8000))+', @States)
' as varchar(8000))
from dbo.sysobjects
where objectproperty(id, 'isusertable')=1
and name<>'tempdb'
exec sp_msforeach_worker '?'
declare hcforeach cursor global
for
select N'
drop trigger '
+cast(quotename('tr_record_'+name) as varchar(8000))
from dbo.sysobjects
where objectproperty(id, 'isusertable')=1
and name<>'tempdb'
exec sp_msforeach_worker '?'