SQL 触发器--用于IP记录转换

用于一个IP转换成数字的函数

ALTER function [dbo].[fn_IP](@ip varchar(40))   returns bigint  
begin   
 return parsename(@ip,4)*cast(16777216 as bigint)+parsename(@ip,3)*65536+parsename(@ip,2)*256+parsename(@ip,1) 
end  

　　

另一个是我与的触发器代码，用于查询IP数据来得到最新的Key 

USE [Win083228X]
GO
/****** Object:  Trigger [dbo].[UpdateIPLID]    Script Date: 08/13/2012 11:30:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[UpdateIPLID]
on [dbo].[VisitedLog] 
after insert
as 
 --保存新插入到VisitedLog表中其在IPLocation表中所对应的LID
 declare @n int;
 --新插入IP的值
 declare @IP varchar(40);
 --保存把IP转换成数字的值
 declare @nIP bigint;
 --新插入VID的值
 declare @nID int;
 --是否第一次登陆
 declare @nFirst tinyint;
 --新插入的IP现在数据库里有多少条
 declare @nNUM int;
 declare @FromSite varchar(10);
 select @nID=VID,@IP=IP,@FromSite=FromSite from inserted
 
 select @nIP=dbo.fn_IP(@IP);
 
 begin try
 select @nNUM=count(*) from VisitedLog where IP=@IP and FromSite=@FromSite
 if @nNUM>1
  set @nFirst=0
 else 
  set @nFirst=1
 select @n=IPLocation.LID from IPLocation where @nIP between nIPBegin and nIPEnd;
 Update VisitedLog  set LID=@n,nIP=@nIP,nFirst=@nFirst where VID=@nID
 end try
 begin catch
 Update VisitedLog  set LID=@n where VID='';
 end catch

转载于:https://www.cnblogs.com/gzh4455/archive/2012/08/13/2636128.html