WEB开发网
开发学院数据库MSSQL Server trigger 的制作 阅读

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

--我们有一个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

Tags:trigger 制作

编辑录入:coldstar [复制链接] [打 印]
赞助商链接