trigger 的制作
2007-11-11 09:54:21 来源:WEB开发网--我们有一个table如下,需要跟踪修改对该表的insert/update/delete操作:
create table testMonitor(c1 int, c2 char(10))
--创建的辅助表如下:
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)
)
--创建的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
--如果我们执行如下的语句:
insert into testmonitor values(1,'aaa')
update testmonitor set c2 = 'bbb'
delete from testmonitor
--您再查询辅助表,就能看到对表修改的相关信息:
select * from tempLog_testMonitor
更多精彩
赞助商链接