Início / dba / Perguntas / 125617
Accepted
Mohamed Mahyoub
Asked: 2016-01-09 02:40:19 +0800 CST

Como posso descartar todos os gatilhos em um único banco de dados?

  • 772

Tenho um banco de dados com 104 triggers, existe alguma forma de deletar todos os triggers com um único comando de um único banco de dados chamado 'system_db_audits?

sql-server sql-server-2008

4 respostas

  1. Best Answer

    Você pode usar o SQL dinâmico e o sys.triggersDMV para criar uma consulta que pode ser executada.

    is_ms_shippedexclui quaisquer gatilhos que foram enviados com o SQL Server.
    parent_class_descfiltros para gatilhos de nível de objeto, em vez de nível de banco de dados.

    Altere o PRINTpara an EXECquando estiver satisfeito com a saída.

    USE system_db_audits;
GO

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += 
    N'DROP TRIGGER ' + 
    QUOTENAME(OBJECT_SCHEMA_NAME(t.object_id)) + N'.' + 
    QUOTENAME(t.name) + N'; ' + NCHAR(13)
FROM sys.triggers AS t
WHERE t.is_ms_shipped = 0
  AND t.parent_class_desc = N'OBJECT_OR_COLUMN';

PRINT @sql;
    • 35

  2. Use Sys.TriggersMeta Data Table que contém uma linha para cada objeto que é um gatilho

    1. Altere o modo de saída para texto clicando no botão da barra de ferramentas mostrado aqui:

    insira a descrição da imagem aqui

    1. Execute este script:

      USE YourDBName
GO
SELECT ' GO ' + Char(10) + Char(13) + 'DROP TRIGGER ' 
    + QUOTENAME(OBJECT_SCHEMA_NAME(O.[object_id])) + '.' 
    + QUOTENAME(name)
FROM sys.sql_modules as M 
    INNER JOIN sys.triggers as O 
        ON M.object_id = O.object_id;

    2. Copie a saída para uma nova janela do SQL Server Management Studio, verifique se o código executa as ações que você espera e execute.

    • 6

  3. Caso você queira executar um trabalho sql em um servidor central [ServerA] para fazer o trabalho de exclusão do gatilho, fornecerei uma versão do PowerShell supondo que você tenha uma instância do SQL Server 2012 (ou superior) com o módulo SQLPS instalado no [ServerA]

    Digamos que você queira excluir todos os gatilhos no banco de dados [AdventureWorks] na instância [ServerB] do SQL Server (SQL Server 2005+).

    Você pode executar o seguinte PS em [ServerA]:

    import-module sqlps -DisableNameChecking;
$db=get-item -Path "sqlserver:\sql\ServerB\default\databases\AdventureWorks";

#before deletion, you can check that triggers do exist
$db.tables.triggers | select name

#now delete
$db.tables.triggers |Where-Object {-not $_.IsSystemObject } | foreach-object {$_.drop()};

#check after deletion
$db.tables.triggers | select name;

    Lembre-se de substituir ServerB e AdventureWorks por seus próprios valores.

    Esta é uma solução bastante flexível que você pode personalizar facilmente para se adaptar a outros requisitos diferentes, como apenas excluir gatilhos pertencentes a um conjunto específico de tabelas ou desabilitar (em vez de excluir) alguns gatilhos específicos etc.

    Estritamente falando, as soluções fornecidas por @Mark Sinkinson não estão corretas porque o requisito não é excluir gatilhos no banco de dados 'system_db_audits', mas excluir gatilhos em outro banco de dados de 'system_db_audits'. Isso significa que você precisa criar um sql dinâmico em 'system_db_audits' para envolver o "sql dinâmico" fornecido por @Mark Sinkinson para excluir esses gatilhos de destino, assumindo que 'system_db_audits' e o banco de dados de destino estão na mesma instância do sql server. Caso contrário, se os dois dbs não estiverem na mesma instância, será ainda muito "feio" lidar com a exclusão (como via servidor vinculado etc.). Nesse cenário, o PS é uma solução elegante, não importa onde o banco de dados de destino esteja ou não na mesma instância sql.

    • 2

  4. Para levar em conta o limite da string que contém a string sql:

    SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON;
SET DATEFORMAT YMD;
SET XACT_ABORT ON;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION

DECLARE @items AS CURSOR
DECLARE @item AS nvarchar(250)

SET @items = CURSOR FOR SELECT N'DROP TRIGGER ' + QUOTENAME(OBJECT_SCHEMA_NAME(t.object_id)) + N'.' + QUOTENAME(t.name) + N'; ' 
    FROM sys.triggers AS t WHERE t.is_ms_shipped = 0 AND t.parent_class_desc = N'OBJECT_OR_COLUMN';

OPEN @items
FETCH NEXT FROM @items INTO @item
WHILE @@FETCH_STATUS = 0
BEGIN    
    EXEC (@item)
    FETCH NEXT FROM @items INTO @item
END 
CLOSE @items 
DEALLOCATE @items

COMMIT TRANSACTION;

    Para Views o comando SET seria:

    SET @items = CURSOR FOR SELECT N'DROP VIEW ' + QUOTENAME(OBJECT_SCHEMA_NAME(r.object_id)) + N'.' + QUOTENAME(r.name) + N'; '
FROM sys.views AS r WHERE r.is_ms_shipped = 0;

    Para Stored Procedures o comando SET seria:

    SET @items = CURSOR FOR SELECT N'DROP PROCEDURE ' + QUOTENAME(OBJECT_SCHEMA_NAME(t.object_id)) + N'.' + QUOTENAME(t.name) + N'; '
    FROM sys.procedures AS t WHERE t.is_ms_shipped = 0
    • 0

relate perguntas