Início / dba / Perguntas / 211447
Accepted
SHR
Asked: 2018-07-06 06:18:25 +0800 CST

TRUNCATE TABLE pode ser sobrecarregado no SQL Server?

  • 772

Eu sei que pode parecer uma pergunta estranha...

Eu tenho um banco de dados e serviços de terceiros e quero monitorá-lo por gatilhos DML em e INSERTde cada tabela.UPDATEDELETE

Mas infelizmente, estou com um problema quando TRUNCATE TABLEé chamado, pois não está ativando os DELETEgatilhos.

Estou procurando uma maneira de "sobrecarregar" o TRUNCATE TABLEpara ser algo como:

DELETE FROM X; --remove all rows
TRUNCATE TABLE X; --truncate the empty table

Ou talvez ative algum gatilho DDL antes de truncar, para que eu possa verificar quais linhas serão truncadas.

Ficarei feliz se houver outras propostas também.

Algo assim é possível?

EDITAR

Eu não quero desabilitar o truncateque eu só quero monitorar os dados.

É um banco de dados de terceiros e foi usado por serviços sobre os quais não posso controlar, portanto, não posso impedir que o truncate.

Estou apenas tentando monitorar as alterações e atualmente não consigo rastrear a exclusão de registros causada pelo truncatecomando.

Obrigado!

sql-server truncate

1 respostas

  1. Best Answer

    Estou procurando uma maneira de "sobrecarregar" a TRUNCATE TABLE para ser algo como:

    APAGAR DE X; --remove todas as linhas TRUNCATE TABLE X; --truncar a tabela vazia

    Ao excluir de uma tabela, você está removendo linhas. Você pode especificar quais linhas excluir com uma WHEREcláusula. Quando você TRUNCATEcria uma tabela, ela não registra quais linhas são excluídas e executa um registro mínimo. TRUNCATEé uma ótima maneira de limpar uma tabela sem causar um balão nos logs de transações.

    Ou talvez ative algum gatilho DDL antes de truncar, para que eu possa verificar quais linhas serão truncadas.

    Você não precisa de um gatilho para isso. A TRUNCATEremoverá todas as linhas da tabela, você sabe que todas as linhas serão removidas no processo. Sem sobreviventes.

    Se você quiser capturar o evento para saber quem truncou o quê ou quando, você pode fazer isso com o SQL Audit, conforme mostrado por Mohd Sufian no SQL Ship .

    Eu não acredito que você possa disparar o gatilho de um Truncate, aqui está a lista DDL no MSDN de quais eventos DDL podem ser disparados de um gatilho.

    Você também pode usar eventos estendidos para capturar Truncateeventos como mostra Jonathan Kehayias no MSDN.

    Se você deseja impedir que os usuários executem Truncate, você pode criar uma exibição ou outro objeto com SCHEMABINDING. Isso evita que as instruções DDL alterem a tabela em questão, que inclui Truncate.

    Uma última observação Truncateé que, se você tiver uma coluna de identidade, ela propagará novamente o valor. Já vi pessoas usarem a RESEEDfunção e Truncatejuntas. Eles irão capturar a última identidade usada, armazená-la em uma tabela de variáveis, truncar a tabela removendo todos os registros com log mínimo e redefinindo a coluna de identidade, então propagarão novamente o último incremental. Normalmente, se você truncar, não se importa com a nova semente. (Nem sempre é verdade, mas geralmente.)

    Eu trunco ​​principalmente em tabelas de preparo ou quando preciso redefinir uma instância para teste. Raramente trunco ​​tabelas de outra forma porque me importo com a integridade dos dados referenciais e quais linhas são removidas das tabelas. Quando eu não me importo com nenhum, eu trunco.

    Eu recomendo ler mais sobre ambos Deletee Truncate, eles alcançarão resultados semelhantes, mas são duas coisas muito diferentes.

    Atul Gaikwad em MSSQLTips escreveu um artigo rápido e agradável sobre algumas dessas diferenças.

    Agora vejamos esta questão:

    Posso contar as linhas que serão afetadas pelo truncar de um aplicativo de terceiros?

    Idealmente, o evento de truncar é agendado ou pode ser planejado. Ainda mais idealmente, você poderá alterar o código ou adicionar algo para que ele possa registrar informações para você. A partir do seu post, vou assumir que você não pode planejar, agendar ou alterar o código.

    Então, como você controla quais linhas serão afetadas por truncar se necessário, com absoluta certeza?

    Eu pessoalmente criaria duas tabelas, uma trigger e uma procedure.

    O gatilho seria baseado em tarefas de inserção ou exclusão e inseriria em uma tabela o número de linhas afetadas, o nome da tabela e a data e hora. Portanto, se o seu processo inserisse 6 linhas, o gatilho inseriria o valor 6, indicando que 6 novos registros entraram. Se você excluísse 10 linhas, ele inseriria um valor de -10, indicando que 10 registros desapareceram.

    Eu gostaria de criar uma coluna extra para notas ou eventos. Eu gostaria que os eventos de auditoria ou estendidos registrassem nesta tabela o dia/hora em que o truncado ocorreu. Eles não poderão dizer quantas linhas, mas podem dizer quando e quem. Isso é o que é realmente importante neste momento.

    A segunda tabela seria um agregado da primeira tabela, pois suponho que você possa ter muitas transações em um dia. Isso informaria o total líquido de registros no final do dia e também informaria quando ocorreram truncamentos. Isso significa que você saberia quantos registros estavam na tabela antes do truncamento, atendendo aos seus requisitos.

    O procedimento faria a agregação entre as tabelas e também realizaria o trabalho de limpeza na primeira tabela.

    • 5

relate perguntas