一个有用的触发器

这个触发器主要是值更改前后的获取，以及字符串分隔

CREATE TRIGGER historyaddress  ON dbo.gdzclist
FOR UPDATE
AS
if UPDATE(chrnowaddress)
begin
declare @chrnowaddress varchar(255),@intID integer,@chrhistoryaddress varchar(500),@regtime datetime,@i integer,@tmptime varchar(500)
select @tmptime=chrnowaddress from inserted
select @chrnowaddress=chrnowaddress, @intID=ID,@chrhistoryaddress=chrhistoryaddress,@regtime=dtmregtime from deleted
if (@chrnowaddress='' )
   return
if (@chrnowaddress=@tmptime)
   return
 if ( rtrim(@chrhistoryaddress+'')=''  or  @chrhistoryaddress is null)
 begin
   select @chrnowaddress=@chrnowaddress+'['+convert(char(4),year(@regtime))+'-'+convert(varchar(2),month(@regtime))+'-'+convert(varchar(2),day(@regtime))+'--'+convert(char(4),year(getdate()))+'-'+convert(varchar(2),month(getdate()))+'-'+convert(varchar(2),day(getdate()))+']'
  end
       else
 begin
    select @i=PATINDEX('%--%', @chrhistoryaddress)
    select @tmptime=substring(@chrhistoryaddress,@i+2,len(@chrhistoryaddress)-@i-2)
      
     WHILE (PATINDEX('%--%', @tmptime)<>0)
  BEGIN
  select @i=PATINDEX('%--%', @tmptime)
  select @tmptime=substring(@tmptime,@i+2,len(@tmptime)-@i-1)
  END
   select @chrnowaddress=@chrhistoryaddress+';'+@chrnowaddress+'['+convert(char(4),year(@tmptime))+'-'+convert(varchar(2),month(@tmptime))+'-'+convert(varchar(2),day(@tmptime))+'--'+convert(char(4),year(getdate()))+'-'+convert(varchar(2),month(getdate()))+'-'+convert(varchar(2),day(getdate()))+']'
 end
update gdzclist set chrhistoryaddress=@chrnowaddress where id=@intID
end

转载于:https://www.cnblogs.com/isMe/archive/2005/11/07/270583.html