trigger 的製作

來源:互聯網
上載者:User

--我們有一個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


相關文章

Beyond APAC's No.1 Cloud

19.6% IaaS Market Share in Asia Pacific - Gartner IT Service report, 2018

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。