--核心参考

--SqlServer触发器 ，目的是记录操作内容

 begin

      declare @di table(et varchar(200), pt varchar(200),  ei varchar(max))

                    insert into @di exec('dbcc inputbuffer(@@spid)')

      declare @op varchar(10)  select @op=case when exists(select 1 from inserted) and exists(select 1 from deleted)

              then 'Update'

              when exists(select 1 from inserted) and not exists(select 1 from deleted)

              then 'Insert'

              when not exists(select 1 from inserted) and exists(select 1 from deleted)

              then 'Delete'

              end

      if @op in('Update','Insert')

         begin

             insert into synchronization.dbo.synchro_log  (operate,spid,login_name,prog_name,hostname,ipaddress,runsql,UDate)

             select @op,@@spid,

                      (select login_name from sys.dm_exec_sessions where session_id=@@spid),

                      (select program_name from sys.dm_exec_sessions where session_id=@@spid),

                      (select hostname from sys.sysprocesses where spid=@@spid),

                      (select client_net_address from sys.dm_exec_connections where session_id=@@spid),

                      (select top 1 isnull(ei,'') from @di),  getdate()

              from inserted n

      end

      else

          begin

                    insert into synchronization.dbo.synchro_log (operate,spid,login_name,prog_name,hostname,ipaddress,runsql,UDate)

                    select @op,@@spid,

                          (select login_name from sys.dm_exec_sessions where session_id=@@spid),

                          (select program_name from sys.dm_exec_sessions where session_id=@@spid),

                          (select hostname from sys.sysprocesses where spid=@@spid),

                          (select client_net_address from sys.dm_exec_connections where session_id=@@spid),

                          (select top 1 isnull(ei,'') from @di),

                          getdate()

            from deleted o

      end

end  

--创建记录表

CREATE TABLE T_SHALL_LOG (

    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 T_SHALL (

    A INT

    , B VARCHAR(10)

    )

GO

---创建触发器

CREATE TRIGGER TR_T_SHALL ON T_SHALL --需要监控的表名

FOR INSERT

    , DELETE

    , UPDATE

AS

--ROWCNT

DECLARE @ROWS INT

SET @ROWS = @@ROWCOUNT

SET NOCOUNT ON

--SQL

CREATE TABLE #T (

    EVENTTYPE VARCHAR(20)

    , PARAMETERS INT

    , EVENTINFO VARCHAR(300)

    ) ----用临时表保存exec('DBCC INPUTBUFFER...')的返回倿

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 T_SHALL_LOG (

    SQL

    , USERID

    , HOSTNAME

    , LOGINID

    , APPNAME

    , OPTYPE

    , ROWCNT

    )

SELECT EVENTINFO

    , @USERID

    , @HOSTNAME

    , @LOGINID

    , @APPNAME

    , @OPTYPE

    , @ROWS

FROM #T

GO

----DML操作测试

SELECT *

FROM t_shall_log;

SELECT *

FROM t_shall;

INSERT t_shall

VALUES (11, 'zhong'    );

INSERT t_shall

VALUES (12    , 'shall')

GO

INSERT t_shall

SELECT A + 2

    , B + ''

FROM t_shall

GO

BEGIN TRANSACTION

    UPDATE t_shall

    SET A = 100    , B = ''

    WHERE A = 11

COMMIT TRANSACTION

GO

DELETE t_shall WHERE A = 100

GO

SELECT * FROM t_shall_log;

SELECT * FROM t_shall;

INSERT dbo.T_SHALL

VALUES (1000, 'sa')

GO

DELETE dbo.T_SHALL

GO

--核验

select * from dbo.T_SHALL

go

select * from dbo.T_SHALL_LOG

go