系统 触发器

系统 触发器

USE [jiFen ]
GO
/****** Object: Trigger [dbo].[trigger_EmpInfo] Script Date: 03/21/2016 17:26:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--触发器
ALTER TRIGGER [dbo].[trigger_EmpInfo] ON [dbo].[EmpInfo]
AFTER UPDATE
AS
DECLARE @t TABLE
(
eventtype NVARCHAR(500) ,
prama SMALLINT ,
eventinfo NVARCHAR(MAX)
)

INSERT  INTO @t  
        ( eventtype ,  
          prama ,  
          eventinfo  
        )  
        EXEC ( 'DBCC INPUTBUFFER(' + @@spid + ')'  
            )  

INSERT  INTO jfEmpinfoUpdate  
        SELECT  @@SPID ,  
                'UPDATE' ,  
                ( SELECT  top 1  eventinfo  
                  FROM      @t  
                ) ,  
                ( SELECT top 1  connect_time  
                  FROM      sys.dm_exec_connections  
                  WHERE     session_id = @@spid  
                ) ,  
                ( SELECT   top 1  net_transport  
                  FROM      sys.dm_exec_connections  
                  WHERE     session_id = @@spid  
                ) ,  
                ( SELECT   top 1  protocol_type  
                  FROM      sys.dm_exec_connections  
                  WHERE     session_id = @@spid  
                ) ,  
                ( SELECT   top 1  auth_scheme  
                  FROM      sys.dm_exec_connections  
                  WHERE     session_id = @@spid  
                ) ,  
                ( SELECT   top 1  client_net_address  
                  FROM      sys.dm_exec_connections  
                  WHERE     session_id = @@spid  
                ) ,  
                ( SELECT    top 1 client_tcp_port  
                  FROM      sys.dm_exec_connections  
                  WHERE     session_id = @@spid  
                ) ,  
                ( SELECT    top 1 local_net_address  
                  FROM      sys.dm_exec_connections  
                  WHERE     session_id = @@spid  
                ) ,  
                ( SELECT    top 1 local_tcp_port  
                  FROM      sys.dm_exec_connections  
                  WHERE     session_id = @@spid  
                ) ,  
                ( SELECT    top 1 [host_name]  
                  FROM      sys.dm_exec_sessions  
                  WHERE     session_id = @@spid  
                ) ,  
                ( SELECT   top 1  [program_name]  
                  FROM      sys.dm_exec_sessions  
                  WHERE     session_id = @@spid  
                ) ,  
                ( SELECT   top 1  host_process_id  
                  FROM      sys.dm_exec_sessions  
                  WHERE     session_id = @@spid  
                ) ,  
                ( SELECT   top 1  client_interface_name  
                  FROM      sys.dm_exec_sessions  
                  WHERE     session_id = @@spid  
                ) ,  
                GETDATE()

posted on 2016-03-21 18:01 影子博客 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/VictorBlog/p/5302968.html