trigger 的制作

王朝mssql·作者佚名  2006-12-17
窄屏简体版  字體: |||超大  

trigger 的制作

trigger 的制作 --我们有一个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

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航