sql server触发器After Update中获取客户端IP，名称及更新前后的数据

触发器 DELETED   临时表可以取得更新前的旧数据

             INSERTED 临时表可以取得更新后的新数据

alter TRIGGER yk_kcTrace
   ON   Yk_CeDrugstore
   AFTER UPDATE
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;
 declare @dt datetime
 DECLARE @SL1 INT
 DECLARE @HOSTNAME VARCHAR(10)
 DECLARE @IP_Address varchar(255); 

 SET @HOSTNAME=HOST_NAME()
 SELECT @IP_Address = client_net_address 
     FROM sys.dm_exec_connections 
     WHERE Session_id = @@SPID; 

 
 set @dt=getdate()
-- SELECT @SL1 = Repertory FROM DELETED   
    
    INSERT INTO YK_KCLOG (trdt ,stcode ,medcode ,mscode ,sl1 ,sl2 ,sl3 ,hostName ,HostIp  )
     select   @dt ,A.stcode ,A.medcode ,A.mscode ,B.Repertory ,A.Repertory-B.Repertory ,A.Repertory ,@HOSTNAME ,@IP_Address
     from INSERTED a,     DELETED B
     WHERE A.STCODE=B.STCODE AND A.MEDCODE=B.MEDCODE AND A.MSCODE=B.MSCODE
    
      
 

END
GO