Jak mogę usunąć wszystkie wyzwalacze w jednej bazie danych?

17

Mam bazę danych ze 104 wyzwalaczami. Czy istnieje sposób, aby usunąć wszystkie wyzwalacze za pomocą jednego polecenia z pojedynczej bazy danych o nazwie „system_db_audits?

sql-server sql-server-2008 trigger

— Mohamed Mahyoub
źródło

29

Możesz użyć Dynamicznego SQL i sys.triggers DMV do zbudowania zapytania, które możesz wykonać.

is_ms_shippedwyklucza wszelkie wyzwalacze dostarczone z SQL Server.
parent_class_descfiltry dla wyzwalaczy poziomu obiektu, a nie poziomu bazy danych.

Zmiana PRINTdo EXECraz jesteś zadowolony z wyniku.

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;

— Mark Sinkinson
źródło

5

Użyj Sys.Triggerstabeli metadanych, która zawiera wiersz dla każdego obiektu będącego wyzwalaczem

Zmień tryb wyjściowy na tekst, klikając pokazany tutaj przycisk paska narzędzi:

Uruchom ten skrypt:

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;

Skopiuj dane wyjściowe do nowego okna SQL Server Management Studio, sprawdź, czy kod wykonuje oczekiwane działania, i wykonaj.

— AA.SC
źródło

Czy jednak DROP TRIGGERoświadczenia nie wymagają terminatorów ;?

— ypercubeᵀᴹ

MSDN mówi: Terminator instrukcji Transact-SQL. Mimo że średnik nie jest wymagany dla większości instrukcji w tej wersji SQL Server, będzie on wymagany w przyszłej wersji.

— AA.SC

2

W przypadku, gdy chcesz uruchomić zadanie SQL na serwerze centralnym [ServerA] w celu usunięcia wyzwalacza, dostarczę wersję programu PowerShell przy założeniu, że masz instancję SQL Server 2012 (lub nowszą) z modułem SQLPS zainstalowanym na [ServerA]

Powiedz, że chcesz usunąć wszystkie wyzwalacze z bazy danych [AdventureWorks] w instancji [ServerB] SQL Server (SQL Server 2005+).

Możesz uruchomić następujący PS na [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;

Pamiętaj, aby zastąpić ServerB i AdventureWorks własnymi wartościami.

Jest to dość elastyczne rozwiązanie, które można łatwo dostosować, aby dostosować się do innych różnych wymagań, takich jak tylko usuwanie wyzwalaczy należy do określonego zestawu tabel lub wyłączenie (zamiast usuwania) niektórych określonych wyzwalaczy itp.

Ściśle mówiąc, rozwiązania dostarczone przez @Mark Sinkinson nie są poprawne, ponieważ wymaganiem nie jest usuwanie wyzwalaczy z bazy danych „system_db_audits”, ale usuwanie wyzwalaczy z innej bazy danych z „system_db_audits”. Oznacza to, że musisz utworzyć dynamiczny sql w 'system_db_audits', aby owinąć „dynamiczny sql” dostarczony przez @Mark Sinkinson, aby usunąć te wyzwalacze docelowe, zakładając, że zarówno „system_db_audits”, jak i docelowa baza danych znajdują się w tej samej instancji serwera sql. W przeciwnym razie, jeśli dwa dbs nie są w tej samej instancji, obsługa usuwania będzie nawet „brzydka” (np. Przez połączony serwer itp.). W takim scenariuszu PS jest eleganckim rozwiązaniem bez względu na to, gdzie docelowa baza danych znajduje się w tej samej instancji SQL.

— jyao
źródło