一个有用的触发器

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

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