Array
(
)

Triggers de auditoria

Darcio Junior,
|
MVP
    30 mai 2012

Bom fiz alguns triggers para gerar auditoria de um SQL server 2008 R2 Express (já que na express não se pode usar CDC)
O problema é o seguinte,quando um delete,insert ou update de diversos valores de uma só vez ele me armazena apenas a 1º modificação...
Ex:
Usuário sa insere 3 funcionários João,José e Maria,a tabela de auditoria me mostra apenas João que foi o 1º,isso acontece também com update e delete...
E ele também não me mostra qual operação de fato ele realizou,se foi um insert um update ou delete,apenas me mostra que foi feita,gostaria de saber como eu posso solucionar esse problema de não mostrar todos os registros e como posso incluir para saber qual comando foi executado.
Grato
Segue o código:
http://pastebin.com/tztJrW2n

#Código

--Duplico a tabela que quero auditar
create table [AuditoriaFornecedores]
(
  [CodigoFornecedor] smallint null,
  [Nome] varchar(60) null,
  [RazaoSocial] varchar(60) null,
  [Endereco] varchar(50) null,
  [Numero] varchar(10) null,
  [Complemento] varchar(150) null,
  [Bairro] varchar(30) null,
  [Cidade] varchar(30) null,
  [UF] varchar(2) null,
  [CEP] varchar(8) null,
  [IE] varchar(16) null,
  [Telefone1] varchar(14) null,
  [Telefone2] varchar(14) null,
  [CaixaPostal] varchar(5) null,
  [Site] varchar(40) null,
  [Email] varchar(30) null,
  [Obs] varchar(max) null,
  [Inativo] bit null,
  [CodigoUF] tinyint null,
  [CodigoCidade] int null,
  [FJ] tinyint null,
  [CNPJCPF] varchar(14) null,
  [AuditoriaDataHora] datetime not null,
  [AuditoriaUsuario] varchar(15) not null
)
--------Fim da duplicação da tabela-----
--E crio os seguintes triggers
go
create trigger AuditoriaFornecedoresInsertUpdateTrigger on Fornecedores
    after insert,update
      as
  declare @@CodigoFornecedor smallint
  declare @@Nome varchar(60)
  declare @@RazaoSocial varchar(60)
  declare @@Endereco varchar(50)
  declare @@Numero varchar(10)
  declare @@Complemento varchar(150)
  declare @@Bairro varchar(30)
  declare @@Cidade varchar(30)
  declare @@UF varchar(2)
  declare @@CEP varchar(8)
  declare @@IE varchar(16)
  declare @@Telefone1 varchar(14)
  declare @@Telefone2 varchar(14)
  declare @@CaixaPostal varchar(5)
  declare @@Site varchar(40)
  declare @@Email varchar(30)
  declare @@Obs varchar(max)
  declare @@Inativo bit
  declare @@CodigoUF tinyint
  declare @@CodigoCidade int
  declare @@FJ tinyint
  declare @@CNPJCPF varchar(14)
begin
  select

  @@CodigoFornecedor = [CodigoFornecedor],
  @@Nome = [Nome],
  @@RazaoSocial = [RazaoSocial],
  @@Endereco = [Endereco],
  @@Numero = [Numero],
  @@Complemento = [Complemento],
  @@Bairro = [Bairro],
  @@Cidade = [Cidade],
  @@UF = [UF],
  @@CEP = [CEP],
  @@IE = [IE],
  @@Telefone1 = [Telefone1],
  @@Telefone2 = [Telefone2],
  @@CaixaPostal = [CaixaPostal],
  @@Site = [Site],
  @@Email = [Email],
  @@Obs = [Obs],
  @@Inativo = [Inativo],
  @@CodigoUF = [CodigoUF],
  @@CodigoCidade = [CodigoCidade],
  @@FJ = [FJ],
  @@CNPJCPF = [CNPJCPF]
from
  inserted
insert into AuditoriaFornecedores
(
  [CodigoFornecedor],
  [Nome],
  [RazaoSocial],
  [Endereco],
  [Numero],
  [Complemento],
  [Bairro],
  [Cidade],
  [UF],
  [CEP],
  [IE],
  [Telefone1],
  [Telefone2],
  [CaixaPostal],
  [Site],
  [Email],
  [Obs],
  [Inativo],
  [CodigoUF],
  [CodigoCidade],
  [FJ],
  [CNPJCPF],
  [AuditoriaDataHora],
  [AuditoriaUsuario]
)
  values
(
  @@CodigoFornecedor,
  @@Nome,
  @@RazaoSocial,
  @@Endereco,
  @@Numero,
  @@Complemento,
  @@Bairro,
  @@Cidade,
  @@UF,
  @@CEP,
  @@IE,
  @@Telefone1,
  @@Telefone2,
  @@CaixaPostal,
  @@Site,
  @@Email,
  @@Obs,
  @@Inativo,
  @@CodigoUF,
  @@CodigoCidade,
  @@FJ,
  @@CNPJCPF,
  GetDate(),
  System_User
)
end
go
create trigger AuditoriaFornecedoresDeleteTrigger on Fornecedores
  after Delete
as
  declare @@CodigoFornecedor smallint
  declare @@Nome varchar(60)
  declare @@RazaoSocial varchar(60)
  declare @@Endereco varchar(50)
  declare @@Numero varchar(10)
  declare @@Complemento varchar(150)
  declare @@Bairro varchar(30)
  declare @@Cidade varchar(30)
  declare @@UF varchar(2)
  declare @@CEP varchar(8)
  declare @@IE varchar(16)
  declare @@Telefone1 varchar(14)
  declare @@Telefone2 varchar(14)
  declare @@CaixaPostal varchar(5)
  declare @@Site varchar(40)
  declare @@Email varchar(30)
  declare @@Obs varchar(max)
  declare @@Inativo bit
  declare @@CodigoUF tinyint
  declare @@CodigoCidade int
  declare @@FJ tinyint
  declare @@CNPJCPF varchar(14)
begin
  Select

  @@CodigoFornecedor = [CodigoFornecedor],
  @@Nome = [Nome],
  @@RazaoSocial = [RazaoSocial],
  @@Endereco = [Endereco],
  @@Numero = [Numero],
  @@Complemento = [Complemento],
  @@Bairro = [Bairro],
  @@Cidade = [Cidade],
  @@UF = [UF],
  @@CEP = [CEP],
  @@IE = [IE],
  @@Telefone1 = [Telefone1],
  @@Telefone2 = [Telefone2],
  @@CaixaPostal = [CaixaPostal],
  @@Site = [Site],
  @@Email = [Email],
  @@Obs = [Obs],
  @@Inativo = [Inativo],
  @@CodigoUF = [CodigoUF],
  @@CodigoCidade = [CodigoCidade],
  @@FJ = [FJ],
  @@CNPJCPF = [CNPJCPF]
from
  Deleted
insert into AuditoriaFornecedores
(
  [CodigoFornecedor],
  [Nome],
  [RazaoSocial],
  [Endereco],
  [Numero],
  [Complemento],
  [Bairro],
  [Cidade],
  [UF],
  [CEP],
  [IE],
  [Telefone1],
  [Telefone2],
  [CaixaPostal],
  [Site],
  [Email],
  [Obs],
  [Inativo],
  [CodigoUF],
  [CodigoCidade],
  [FJ],
  [CNPJCPF],
  [AuditoriaDataHora],
  [AuditoriaUsuario]
)
  values
(
  @@CodigoFornecedor,
  @@Nome,
  @@RazaoSocial,
  @@Endereco,
  @@Numero,
  @@Complemento,
  @@Bairro,
  @@Cidade,
  @@UF,
  @@CEP,
  @@IE,
  @@Telefone1,
  @@Telefone2,
  @@CaixaPostal,
  @@Site,
  @@Email,
  @@Obs,
  @@Inativo,
  @@CodigoUF,
  @@CodigoCidade,
  @@FJ,
  @@CNPJCPF,
  GetDate(),
  System_User
)
end

Cesar Roniglei
   - 06 jun 2012

Bom dia Darcio,

Neste link você verá como funciona o controle de auditoria em tabelas conforme necessita,
de uma olhada e caso não tenha solucionado seu problema é so me avisar.

#Código



__________________________________________________________________
César Roniglei
TI - Tecnologia da Informação

Skype: cesar.roniglei
__________________________________________________________________