trigger 的制作-数据库专栏,SQL Server

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