EXEMPLO_1212310 Database

Parameters

Name	Type	Mode
@nometabela	nvarchar	IN

Definition

CREATE procedure sp_montatriggerupdate @nometabela sysname
as
  declare @variaveis varchar(4000)
  declare @variaveis2 varchar(4000)
  declare @variaveis3 varchar(4000)
  declare @variaveis4 varchar(4000)
  declare @variaveis5 varchar(4000)
  declare @variaveisant1 varchar(4000)
  declare @variaveisant2 varchar(4000)
  declare @variaveisant3 varchar(4000)
  declare @variaveisant4 varchar(4000)
  declare @variaveisant5 varchar(4000)
  declare @listacursor nvarchar(4000)
  declare @listacursor1 nvarchar(4000)
  declare @listacursorant1 nvarchar(4000)
  declare @listacursorant2 nvarchar(4000)
  declare @listacampos nvarchar(4000)
  declare @listacampos1 nvarchar(4000)
  declare @auxcamposlog varchar(4000)
  declare @nomecampo sysname
  declare @aux varchar(4000)
  declare @inserecampos varchar(4000)
  declare @inserecampos1 varchar(4000)
  declare @inserecampos2 varchar(4000)
  declare @inserecampos3 varchar(4000)
  declare @inserecampos4 varchar(4000)
  declare @inserecampos5 varchar(4000)
  declare @inserecampos6 varchar(4000)
  declare @inserecampos7 varchar(4000)
  declare @inserecampos8 varchar(4000)
  declare @inserecampos9 varchar(4000)
  declare @inserecampos10 varchar(4000)
  declare @inserecampos11 varchar(4000)
  declare @inserecampos12 varchar(4000)
  declare @inserecampos13 varchar(4000)
  declare @inserecampos14 varchar(4000)
  declare @inserecampos15 varchar(4000)
  declare @inserecampos16 varchar(4000)
  declare @auxchave varchar(4000)
  declare @textochave varchar(4000)
  declare @idtabela int
  declare @tipocampo varchar(50)
  declare @nometabelacomp varchar(30)
  declare @versao varchar(10)
  declare @camposDefault varchar(500)
  set @nometabelacomp = @nometabela;
  if (len(@nometabelacomp) >= 20)
       	set @nometabelacomp = '_' + substring (@nometabelacomp, len(@nometabelacomp)-19, 30)
  IF not EXISTS (SELECT name FROM sysobjects  WHERE name = 'logupdate'+@nometabelacomp AND type = 'TR')
  begin
  select @idtabela=(select id from sysobjects where name=@nometabela and uid = SCHEMA_ID())
  declare cursorcampos cursor for select c.name from syscolumns c where id=@idtabela
   and xtype<>35 and xtype<>34 and c.name not in (select name from syscolumns where id=@idtabela and (xtype = 167 and length = -1))
  select @listacursor=''
  select @listacursor1=' '
  select @listacursorant1=''
  select @listacursorant2=''
  select @listacampos=''
  select @listacampos1=' '
  select @variaveis=''
  select @variaveis2=''
  select @variaveis3=''
  select @variaveis4=''
  select @variaveis5=''
  select @variaveisant1=''
  select @variaveisant2=''
  select @variaveisant3=''
  select @variaveisant4=''
  select @variaveisant5=''
  select @inserecampos=''
  select @inserecampos1=''
  select @inserecampos2=''
  select @inserecampos3=''
  select @inserecampos4=''
  select @inserecampos5=''
  select @inserecampos6=''
  select @inserecampos7=''
  select @inserecampos8=''
  select @inserecampos9=''
  select @inserecampos10=''
  select @inserecampos11=''
  select @inserecampos12=''
  select @inserecampos13=''
  select @inserecampos14=''
  select @inserecampos15=''
  select @inserecampos16=''
  select @camposDefault =
  ''',''U'',@usrlog,@d_d,@d_d2,@chavelog,@processolog,@networkuser,@machinename,@machineip'+char(10)
  open cursorcampos
  fetch next from cursorcampos into @nomecampo
  while @@fetch_status=0
  begin
    exec sp_tipocampo @idtabela,@nomecampo,@tipocampo output
    if len(@variaveis)<3700
     select @variaveis=@variaveis + 'declare @'+@nomecampo+' '+@tipocampo+char(10)
    else if len(@variaveis2)<3700
     select @variaveis2=@variaveis2 + 'declare @'+@nomecampo+' '+@tipocampo+char(10)
     else if len(@variaveis3)<3700
     select @variaveis3=@variaveis3 + 'declare @'+@nomecampo+' '+@tipocampo+char(10)
     else if len(@variaveis4)<3700
     select @variaveis4=@variaveis4 + 'declare @'+@nomecampo+' '+@tipocampo+char(10)
     else select @variaveis5=@variaveis5 + 'declare @'+@nomecampo+' '+@tipocampo+char(10)
    if len(@variaveisant1)<3700
     select @variaveisant1=@variaveisant1 + 'declare @ant'+@nomecampo+' '+@tipocampo+char(10)
    else if len(@variaveisant2)<3700
     select @variaveisant2=@variaveisant2 + 'declare @ant'+@nomecampo+' '+@tipocampo+char(10)
     else if len(@variaveisant3)<3700
     select @variaveisant3=@variaveisant3 + 'declare @ant'+@nomecampo+' '+@tipocampo+char(10)
     else if len(@variaveisant4)<3700
     select @variaveisant4=@variaveisant4 + 'declare @ant'+@nomecampo+' '+@tipocampo+char(10)
     else select @variaveisant5=@variaveisant5 + 'declare @ant'+@nomecampo+' '+@tipocampo+char(10)
    if @listacursor=''
    select @listacursor='@'+@nomecampo
    else if len(@listacursor)<3700
	select @listacursor=@listacursor+',@'+@nomecampo
	else select @listacursor1=@listacursor1+',@'+@nomecampo
    if @listacursorant1=''
    select @listacursorant1='@ant'+@nomecampo
    else if len(@listacursorant1)<3700
         select @listacursorant1=@listacursorant1+',@ant'+@nomecampo
         else select @listacursorant2=@listacursorant2+',@ant'+@nomecampo
    if @listacampos=''
	    select @listacampos=@nomecampo
    else if len(@listacampos)<3700
	select @listacampos=@listacampos+','+@nomecampo
    else select @listacampos1=@listacampos1+','+@nomecampo
    if len(@inserecampos)<3700
      select @inserecampos = @inserecampos+'set @d_d=cast(@'+@nomecampo+' as varchar(4000)) set @d_d2=cast(@ant'+@nomecampo+' as varchar(4000)) if UPDATE('+@nomecampo+') exec sp_campolog '''+@nometabela+''','''+@nomecampo+@camposDefault
    else if len(@inserecampos1)<3700
      select @inserecampos1 = @inserecampos1+'set @d_d=cast(@'+@nomecampo+' as varchar(4000)) set @d_d2=cast(@ant'+@nomecampo+' as varchar(4000)) if UPDATE('+@nomecampo+') exec sp_campolog '''+@nometabela+''','''+@nomecampo+@camposDefault
    else if len(@inserecampos2)<3700
      select @inserecampos2 = @inserecampos2+'set @d_d=cast(@'+@nomecampo+' as varchar(4000)) set @d_d2=cast(@ant'+@nomecampo+' as varchar(4000)) if UPDATE('+@nomecampo+') exec sp_campolog '''+@nometabela+''','''+@nomecampo+@camposDefault
    else if len(@inserecampos3)<3700
      select @inserecampos3 = @inserecampos3+'set @d_d=cast(@'+@nomecampo+' as varchar(4000)) set @d_d2=cast(@ant'+@nomecampo+' as varchar(4000)) if UPDATE('+@nomecampo+') exec sp_campolog '''+@nometabela+''','''+@nomecampo+@camposDefault
    else if len(@inserecampos4)<3700
      select @inserecampos4 = @inserecampos4+'set @d_d=cast(@'+@nomecampo+' as varchar(4000)) set @d_d2=cast(@ant'+@nomecampo+' as varchar(4000)) if UPDATE('+@nomecampo+') exec sp_campolog '''+@nometabela+''','''+@nomecampo+@camposDefault
    else if len(@inserecampos5)<3700
      select @inserecampos5 = @inserecampos5+'set @d_d=cast(@'+@nomecampo+' as varchar(4000)) set @d_d2=cast(@ant'+@nomecampo+' as varchar(4000)) if UPDATE('+@nomecampo+') exec sp_campolog '''+@nometabela+''','''+@nomecampo+@camposDefault
    else if len(@inserecampos6)<3700
      select @inserecampos6 = @inserecampos6+'set @d_d=cast(@'+@nomecampo+' as varchar(4000)) set @d_d2=cast(@ant'+@nomecampo+' as varchar(4000)) if UPDATE('+@nomecampo+') exec sp_campolog '''+@nometabela+''','''+@nomecampo+@camposDefault
    else if len(@inserecampos7)<3700
      select @inserecampos7 = @inserecampos7+'set @d_d=cast(@'+@nomecampo+' as varchar(4000)) set @d_d2=cast(@ant'+@nomecampo+' as varchar(4000)) if UPDATE('+@nomecampo+') exec sp_campolog '''+@nometabela+''','''+@nomecampo+@camposDefault
    else if len(@inserecampos8)<3700
      select @inserecampos8 = @inserecampos8+'set @d_d=cast(@'+@nomecampo+' as varchar(4000)) set @d_d2=cast(@ant'+@nomecampo+' as varchar(4000)) if UPDATE('+@nomecampo+') exec sp_campolog '''+@nometabela+''','''+@nomecampo+@camposDefault
    else if len(@inserecampos9)<3700
      select @inserecampos9 = @inserecampos9+'set @d_d=cast(@'+@nomecampo+' as varchar(4000)) set @d_d2=cast(@ant'+@nomecampo+' as varchar(4000)) if UPDATE('+@nomecampo+') exec sp_campolog '''+@nometabela+''','''+@nomecampo+@camposDefault
    else if len(@inserecampos10)<3700
      select @inserecampos10 = @inserecampos10+'set @d_d=cast(@'+@nomecampo+' as varchar(4000)) set @d_d2=cast(@ant'+@nomecampo+' as varchar(4000)) if UPDATE('+@nomecampo+') exec sp_campolog '''+@nometabela+''','''+@nomecampo+@camposDefault
    else if len(@inserecampos11)<3700
      select @inserecampos11 = @inserecampos11+'set @d_d=cast(@'+@nomecampo+' as varchar(4000)) set @d_d2=cast(@ant'+@nomecampo+' as varchar(4000)) if UPDATE('+@nomecampo+') exec sp_campolog '''+@nometabela+''','''+@nomecampo+@camposDefault
    else if len(@inserecampos12)<3700
      select @inserecampos12 = @inserecampos12+'set @d_d=cast(@'+@nomecampo+' as varchar(4000)) set @d_d2=cast(@ant'+@nomecampo+' as varchar(4000)) if UPDATE('+@nomecampo+') exec sp_campolog '''+@nometabela+''','''+@nomecampo+@camposDefault
    else if len(@inserecampos13)<3700
      select @inserecampos13 = @inserecampos13+'set @d_d=cast(@'+@nomecampo+' as varchar(4000)) set @d_d2=cast(@ant'+@nomecampo+' as varchar(4000)) if UPDATE('+@nomecampo+') exec sp_campolog '''+@nometabela+''','''+@nomecampo+@camposDefault
    else if len(@inserecampos14)<3700
      select @inserecampos14 = @inserecampos14+'set @d_d=cast(@'+@nomecampo+' as varchar(4000)) set @d_d2=cast(@ant'+@nomecampo+' as varchar(4000)) if UPDATE('+@nomecampo+') exec sp_campolog '''+@nometabela+''','''+@nomecampo+@camposDefault
    else if len(@inserecampos15)<3700
      select @inserecampos15 = @inserecampos15+'set @d_d=cast(@'+@nomecampo+' as varchar(4000)) set @d_d2=cast(@ant'+@nomecampo+' as varchar(4000)) if UPDATE('+@nomecampo+') exec sp_campolog '''+@nometabela+''','''+@nomecampo+@camposDefault
    else if len(@inserecampos16)<3700
      select @inserecampos16 = @inserecampos16+'set @d_d=cast(@'+@nomecampo+' as varchar(4000)) set @d_d2=cast(@ant'+@nomecampo+' as varchar(4000)) if UPDATE('+@nomecampo+') exec sp_campolog '''+@nometabela+''','''+@nomecampo+@camposDefault
    fetch next from cursorcampos into @nomecampo
  end
  close cursorcampos
  deallocate cursorcampos
  select @auxchave=''
  declare cursorchave cursor for select c.name from syscolumns c,sysindexes i,sysindexkeys k where
  i.id=@idtabela and K.indid = 1 and i.id=k.id and i.indid=k.indid and c.id=k.id and
  c.colid=k.colid
  open cursorchave
  fetch next from cursorchave into @nomecampo
  while @@fetch_status=0
  begin
    if @auxchave=''
    select @auxchave=''''+@nomecampo+'=''+convert(varchar,@'+@nomecampo+')'
    else select @auxchave=@auxchave+'+'';'+@nomecampo+'=''+convert(varchar,@'+@nomecampo+')'
    fetch next from cursorchave into @nomecampo
  end
  close cursorchave
  deallocate cursorchave
  if @auxchave<>''
  select @textochave='select @chavelog = '+@auxchave
  else select @textochave=''
  exec( N'create trigger logupdate'+@nometabelacomp+' on '+ @nometabela+'
  for update
  as
  set nocount on
    '+@variaveis+' '+@variaveis2+' '+@variaveis3+' '+@variaveis4+' '+@variaveis5+' '
    +@variaveisant1+' '+@variaveisant2+' '+@variaveisant3+' '+@variaveisant4+' '+@variaveisant5+
    '
    declare @usrlog varchar(20)
    declare @chavelog varchar(500)
    declare @d_d varchar(4000)
    declare @d_d2 varchar(4000)
    declare @processolog varchar(100)
    declare @contextinfo varchar(300)
    declare @networkuser varchar(100)
    declare @machinename varchar(100)
    declare @machineip varchar(50)
    declare cursorlog cursor for select '+@listacampos+ @listacampos1+' from inserted
    declare cursorlogant cursor for select '+@listacampos+ @listacampos1+' from deleted
    if (select logativo from zlogparams)=''S''
    begin
      select @contextinfo = context_info from sysprocesses where spid = @@spid
       if @contextinfo = 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
       begin
         	select
         		@usrlog= substring (host_Name() + ''\''+ nt_username, 1,20) ,
	 					@processolog= isnull(substring (program_name,1,100),''Operação fora do Escopo do Log de Auditoria'' )
	 				from 	master..sysprocesses
         	where	spid = @@spid
       end
       else
       begin
					DECLARE @contextinfostring AS VARCHAR(500)
					SET @contextinfostring = convert(varchar(500), @contextinfo)
					SELECT *
					INTO #CURSOR
					FROM SPLIT('';'', @contextinfostring)
					SELECT @usrlog			= VALUE FROM #CURSOR WHERE ROWID = 1
					SELECT @processolog = VALUE FROM #CURSOR WHERE ROWID = 2
					SELECT @networkuser = VALUE FROM #CURSOR WHERE ROWID = 3
					SELECT @machinename = VALUE FROM #CURSOR WHERE ROWID = 4
					SELECT @machineip		= VALUE FROM #CURSOR WHERE ROWID = 5
					DROP TABLE #CURSOR
       end
	   if not exists (SELECT 1 FROM ZLOGEXCECAO (NOLOCK) WHERE CHARINDEX(SERVERNAME, @processolog) > 0)
	   begin
      open cursorlog
      open cursorlogant
      fetch next from cursorlogant into '+@listacursorant1+@listacursorant2+'
      fetch next from cursorlog into '+@listacursor+ @listacursor1+'
      while @@fetch_status=0
      begin
        '+@textochave+'
        '+@inserecampos+' '+@inserecampos1+' '+@inserecampos2+' '+@inserecampos3+' '+
         @inserecampos4+' '+@inserecampos5+' '+@inserecampos6+' '+@inserecampos7+' '+
         @inserecampos8+' '+@inserecampos9+' '+@inserecampos10+' '+@inserecampos11+' '+
         @inserecampos12+' '+@inserecampos13+' '+@inserecampos14+' '+@inserecampos15+' '+
         @inserecampos16+'
        fetch next from cursorlogant into '+@listacursorant1+@listacursorant2+'
        fetch next from cursorlog into '+@listacursor+@listacursor1+'
      end
      close cursorlog
      close cursorlogant
     end
	 end
    deallocate cursorlog
    deallocate cursorlogant
    set nocount off
  ')
  end