首先建一个表T_LOG用来保存日志,假设要监视的表为T_TEST,则创建触发器的代码入下:
create trigger TestTrigger on
T_TEST
for insert,update,delete
as
SET NOCOUNT ON
create table #t(EventType varchar(50),Parameters int ,EventInfo varchar(6000))
Declare @Msg varchar(1000)
declare @spid varchar(20)
set @Msg = 'DBCC INPUTBUFFER ('+@spid+')'
set @spid=cast(@@spid as varchar)
insert #t exec('DBCC INPUTBUFFER ('+@spid+')')
insert T_LOG(AppName,Sql,UserName,HostName,Time)
select App_Name(),
EventInfo,
SYSTEM_USER,
Host_name(),
getdate()
from #t
如果名为TestTrigger的触发器已存在,则把create改成alter,即改为修改.