--触发器如何调用存储过程

create trigger test on table

for insert

as

exec 存储过程名

go

 

--存储过程如何调用触发器

create proc test

as

update table set ...

insert table select ...

delete table ...

 

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12330444/viewspace-448420/，如需转载，请注明出处，否则将追究法律责任。