--我們有一個(gè)table如下，需要跟蹤修改對(duì)該表的insert/update/delete操作：

create table testMonitor(c1 int, c2 char(10))


--創(chuàng)建的輔助表如下：

create table tempLog_testMonitor(
rowID bigint identity(1,1),
hostname nchar(128),
program_name nchar(128),
nt_domain nchar(128),
nt_username nchar(128),
net_address nchar(12),
loginame nchar(128),
login_time datetime,
EventType nvarchar(30),
parameters int,
EventInfo nvarchar(255)
)

--創(chuàng)建的trigger如下：

create trigger trg_testMonitor
on testMonitor
for insert,update,delete
as
begin

??????????? declare @hostname nchar(128)
??????????? declare @program_name nchar(128)?
??????????? declare @nt_domain nchar(128)?
??????????? declare @nt_username nchar(128)?
??????????? declare @net_address nchar(12)
??????????? declare @loginame nchar(128)
??????????? declare @login_time datetime
??????????? declare @rowID bigint

????????????
??????????? insert into tempLog_testMonitor(EventType,parameters,EventInfo)
??????????? exec ('dbcc inputbuffer(@@spid)')
??????????? select @rowID = scope_identity()??????????????????
??????????? select? @hostname = hostname,
??????????????????????? @program_name = program_name,
??????????????????????? @nt_domain = nt_domain,
??????????????????????? @nt_username = nt_username,
??????????????????????? @net_address = net_address,
??????????????????????? @loginame = loginame,
??????????????????????? @login_time = login_time
??????????? from master..sysprocesses where spid = @@spid

??????????? update tempLog_testMonitor set?
??????????? hostname = @hostname,
??????????? program_name = @program_name,
??????????? nt_domain = @nt_domain,
??????????? nt_username = @nt_username,
??????????? net_address = @net_address,
??????????? loginame = @loginame,
??????????? login_time = @login_time
??????????? where rowID = @rowID
end

--如果我們執(zhí)行如下的語句：

insert into testmonitor values(1,'aaa')
update testmonitor set c2 = 'bbb'
delete from testmonitor

--您再查詢輔助表，就能看到對(duì)表修改的相關(guān)信息：

select * from tempLog_testMonitor