34,594
社区成员
发帖
与我相关
我的任务
分享
drop table log
go
CREATE TABLE log(
ID INT identity,
SerialNumber NVARCHAR(128),
Company NVARCHAR(max),
Logged_USER NVARCHAR(128),
UPDATE_USER NVARCHAR(128),
AppName NVARCHAR(128),
spid INT,
local_net_address NVARCHAR(128),
local_tcp_port INT,
client_net_address NVARCHAR(128),
client_tcp_port INT,
UPDATE_TIME DATETIME)
go
drop trigger TR_t1_delete
go
--Trigger
create TRIGGER TR_t1_delete ON t1 WITH EXECUTE AS owner
FOR DELETE
AS
INSERT INTO log(SerialNumber,
Company,
Logged_USER,
UPDATE_USER,
AppName,
spid,
local_net_address,
local_tcp_port,
client_net_address,
client_tcp_port,
UPDATE_TIME)
SELECT A.serialnumber,
A.Company,
ORIGINAL_LOGIN(),
CURRENT_USER ,
APP_NAME() ,
@@spid AS spid,
B.local_net_address ,
B.local_tcp_port ,
B.client_net_address ,
B.client_tcp_port ,
GETDATE()
FROM deleted A inner join sys.dm_exec_connections B
ON B.session_id = @@spid
--功能是监控其他帐号在对表T1做删除时记录当时信息到LOG表
create TRIGGER TR_t1_delete ON t1 WITH EXECUTE AS 'sa'
FOR DELETE
AS
INSERT INTO log(SerialNumber,
Company,
Logged_USER,
UPDATE_USER,
AppName,
spid,
local_net_address,
local_tcp_port,
client_net_address,
client_tcp_port,
UPDATE_TIME)
SELECT A.serialnumber,
A.Company,
ORIGINAL_LOGIN(),
CURRENT_USER ,
APP_NAME() ,
@@spid AS spid,
B.local_net_address ,
B.local_tcp_port ,
B.client_net_address ,
B.client_tcp_port ,
GETDATE()
FROM deleted A inner join sys.dm_exec_connections B
ON B.session_id = @@spid
use master
go
grant view any definition to public