针对某个特定表的操作日志记录

CREATE TABLE TBLOG(

  ID INT NOT NULL IDENTITY(1,1),

  EVTIME DATETIME NOT NULL DEFAULT (GETDATE()), --访问时间

  SQL VARCHAR(300), --执行的SQL语句

  USERID VARCHAR(128) NOT NULL DEFAULT (SUSER_SNAME()), --连接的时候使用的哪个SQL登陆用户

  HOSTNAME VARCHAR(128) NOT NULL DEFAULT (HOST_NAME()), --客户端的机器名

  LOGINID VARCHAR(128), --客户端的登陆用户（OS的用户）

  APPNAME VARCHAR(128) NOT NULL DEFAULT(APP_NAME()), --是从查询分析器，还是应用程序来执行的

  OPTYPE INT, --执行的是INSERT,UPDATE,还是DELETE操作

  ROWCNT INT --影响了多少行数据 )

GO

--测试表

CREATE TABLE TBTEST( A INT, B VARCHAR(10) ) GO

 

--触发器

CREATE TRIGGER TR_TBTEST ON TBTEST FOR INSERT,DELETE,UPDATE

AS

--ROWCNT

DECLARE @ROWS INT SET @ROWS=@@ROWCOUNT

SET NOCOUNT ON

--SQL

DECLARE @T TABLE (EVENTTYPE VARCHAR(20), PARAMETERS INT, EVENTINFO VARCHAR(300))

DECLARE @SPID VARCHAR(20)

SET @SPID=CAST(@@SPID AS VARCHAR) --本次操作的进程号

INSERT @T EXEC('DBCC INPUTBUFFER ('+@SPID+')')

--OPTYPE

DECLARE @OPTYPE INT SET @OPTYPE=2  --UPDATE

IF NOT EXISTS (SELECT 1 FROM INSERTED) SET @OPTYPE=3  --DELETE

IF NOT EXISTS (SELECT 1 FROM DELETED) SET @OPTYPE=1 --INSERT

--进程信息

DECLARE @USERID VARCHAR(128), --连接的时候使用的哪个SQL登陆用户

               @HOSTNAME VARCHAR(128), --客户端的机器名

               @LOGINID VARCHAR(128), --客户端的登陆用户（OS的用户）

               @APPNAME VARCHAR(128) --是从查询分析器，还是应用程序来执行的

SELECT

    @USERID =LOGINAME, --连接的时候使用的哪个SQL登陆用户

    @HOSTNAME =HOSTNAME, --客户端的机器名

    @LOGINID =NT_USERNAME, --客户端的登陆用户（OS的用户）

    @APPNAME =PROGRAM_NAME --是从查询分析器，还是应用程序来执行的

FROM

    MASTER..SYSPROCESSES

WHERE

    SPID=@@SPID

--INSERT

INSERT TBLOG

    (SQL , USERID , HOSTNAME , LOGINID , APPNAME , OPTYPE , ROWCNT )

SELECT EVENTINFO, @USERID , @HOSTNAME , @LOGINID , @APPNAME , @OPTYPE , @ROWS FROM @T

 

GO