CREATE TRIGGER (Transact-SQL)

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Tworzy wyzwalacz DML, DDL lub logowania. Wyzwalacz to specjalny typ procedury składowanej, która jest uruchamiana automatycznie po wystąpieniu zdarzenia na serwerze bazy danych. Kod DML jest uruchamiany, gdy użytkownik próbuje zmodyfikować dane za pomocą zdarzenia języka manipulowania danymi (DML). Zdarzenia DML to INSERT, UPDATElub DELETE instrukcje w tabeli lub widoku. Te wyzwalacze są wyzwalane, gdy jakiekolwiek prawidłowe zdarzenie jest wyzwalane, bez względu na to, czy dotyczy to wierszy tabeli, czy nie. Aby uzyskać więcej informacji, zobacz Wyzwalacze DML.

Wyzwalacze DDL są uruchamiane w odpowiedzi na różne zdarzenia języka definicji danych (DDL). Te zdarzenia odpowiadają przede wszystkim Transact-SQL CREATE, ALTERi DROP instrukcjom oraz niektórym systemowym procedurom składowanych wykonującym operacje podobne do języka DDL.

Wyzwalanie logowania jest wyzwalane w odpowiedzi na LOGON zdarzenie, które jest zgłaszane po ustanowieniu sesji użytkownika. Wyzwalacze można tworzyć bezpośrednio z instrukcji Transact-SQL lub z metod zestawów utworzonych w środowisku uruchomieniowym języka wspólnego programu Microsoft .NET Framework (CLR) i przekazywanych do wystąpienia programu SQL Server. Program SQL Server umożliwia tworzenie wielu wyzwalaczy dla dowolnej konkretnej instrukcji.

Transact-SQL konwencje składni

Składnia

Składnia programu SQL Server

Wyzwalacz na instrukcji INSERT, UPDATElub DELETE w tabeli lub widoku (wyzwalacz DML):

CREATE [ OR ALTER ] TRIGGER [ schema_name . ] trigger_name
ON { table | view }
[ WITH <dml_trigger_option> [ , ...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS { sql_statement  [ ; ] [ , ...n ] | EXTERNAL NAME <method_specifier [ ; ] > }

<dml_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

<method_specifier> ::=
    assembly_name.class_name.method_name

Wyzwalacz w INSERTtabeli , UPDATElub DELETE instrukcji (wyzwalacz DML w tabelach zoptymalizowanych pod kątem pamięci):

CREATE [ OR ALTER ] TRIGGER [ schema_name . ] trigger_name
ON { table }
[ WITH <dml_trigger_option> [ , ...n ] ]
{ FOR | AFTER }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
AS { sql_statement  [ ; ] [ , ...n ] }

<dml_trigger_option> ::=
    [ NATIVE_COMPILATION ]
    [ SCHEMABINDING ]
    [ EXECUTE AS Clause ]

Wyzwalacz na instrukcji CREATE, GRANTALTERDENYDROP, REVOKElub UPDATE (wyzwalacz DDL):

CREATE [ OR ALTER ] TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH <ddl_trigger_option> [ , ...n ] ]
{ FOR | AFTER } { event_type | event_group } [ , ...n ]
AS { sql_statement  [ ; ] [ , ...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

<ddl_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

Wyzwalacz na LOGON zdarzeniu (wyzwalacz logowania):

CREATE [ OR ALTER ] TRIGGER trigger_name
ON ALL SERVER
[ WITH <logon_trigger_option> [ , ...n ] ]
{ FOR | AFTER } LOGON
AS { sql_statement  [ ; ] [ , ...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

<logon_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

Składnia usługi Azure SQL Database

Wyzwalacz na instrukcji INSERT, UPDATElub DELETE w tabeli lub widoku (wyzwalacz DML):

CREATE [ OR ALTER ] TRIGGER [ schema_name . ] trigger_name
ON { table | view }
 [ WITH <dml_trigger_option> [ , ...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
  AS { sql_statement  [ ; ] [ , ...n ] [ ; ] > }

<dml_trigger_option> ::=
        [ EXECUTE AS Clause ]

Wyzwalacz na instrukcji CREATE, GRANTALTERDENYDROP, REVOKElub UPDATE STATISTICS (wyzwalacz DDL):

CREATE [ OR ALTER ] TRIGGER trigger_name
ON { DATABASE }
 [ WITH <ddl_trigger_option> [ , ...n ] ]
{ FOR | AFTER } { event_type | event_group } [ , ...n ]
AS { sql_statement  [ ; ] [ , ...n ]  [ ; ] }

<ddl_trigger_option> ::=
    [ EXECUTE AS Clause ]

Argumenty (w programowaniu)

LUB ALTER

Dotyczy: SQL Server 2016 (13.x) SP1 i nowsze wersje oraz Azure SQL Database

Warunkowo zmienia wyzwalacz tylko wtedy, gdy już istnieje.

schema_name

Nazwa schematu, do którego należy wyzwalacz DML. Wyzwalacze DML są ograniczone do schematu tabeli lub widoku, w którym są tworzone. schema_name nie można określić dla wyzwalaczy DDL lub logowania.

trigger_name

Nazwa wyzwalacza. Trigger_name musi przestrzegać reguł dotyczących identyfikatorów, z tą różnicą, że trigger_name nie może rozpoczynać się od # lub ##.

stół | widok

Tabela lub widok, w którym jest uruchamiany wyzwalacz DML. Ta tabela lub widok jest czasami określany jako tabela wyzwalacza lub widok wyzwalacza. Określenie w pełni kwalifikowanej nazwy tabeli lub widoku jest opcjonalne. Widok można odwoływać się tylko przez INSTEAD OF wyzwalacz. Nie można zdefiniować wyzwalaczy DML w lokalnych lub globalnych tabelach tymczasowych.

BAZA DANYCH

Stosuje zakres wyzwalacza DDL do bieżącej bazy danych. Jeśli zostanie określony, wyzwalacz jest uruchamiany zawsze, gdy event_type lub event_group występuje w bieżącej bazie danych.

WSZYSTKIE SERWERY

Stosuje zakres wyzwalacza DDL lub logowania do bieżącego serwera. Jeśli zostanie określony, wyzwalacz jest uruchamiany zawsze, gdy event_type lub event_group występuje w dowolnym miejscu na bieżącym serwerze.

Z SZYFROWANIEM

Zaciemnia tekst instrukcji CREATE TRIGGER . Użycie WITH ENCRYPTION uniemożliwia publikowanie wyzwalacza w ramach replikacji programu SQL Server. WITH ENCRYPTION Nie można określić dla wyzwalaczy CLR.

WYKONAJ JAKO

Określa kontekst zabezpieczeń, w którym jest wykonywany wyzwalacz. Umożliwia kontrolowanie konta użytkownika używanego przez wystąpienie programu SQL Server w celu zweryfikowania uprawnień do wszystkich obiektów bazy danych, do których odwołuje się wyzwalacz.

Ta opcja jest wymagana w przypadku wyzwalaczy w tabelach zoptymalizowanych pod kątem pamięci.

Aby uzyskać więcej informacji, zobacz EXECUTE AS Clause (Klauzula EXECUTE AS).

NATIVE_COMPILATION

Wskazuje, że wyzwalacz jest natywnie skompilowany.

Ta opcja jest wymagana w przypadku wyzwalaczy w tabelach zoptymalizowanych pod kątem pamięci.

POWIĄZANIE SCHEMATU

Gwarantuje, że tabele, do których odwołuje się wyzwalacz, nie mogą zostać usunięte ani zmienione.

Ta opcja jest wymagana w przypadku wyzwalaczy w tabelach zoptymalizowanych pod kątem pamięci i nie jest obsługiwana w przypadku wyzwalaczy w tradycyjnych tabelach.

FOR | PO

FOR lub AFTER określa, że wyzwalacz DML jest uruchamiany tylko wtedy, gdy wszystkie operacje określone w instrukcji wyzwalającej SQL zostały uruchomione pomyślnie. Wszystkie akcje kaskadowe i kontrole ograniczeń odwołań muszą również zakończyć się powodzeniem przed wyzwoleniem tego wyzwalacza.

Nie można definiować AFTER wyzwalaczy w widokach.

ZAMIAST

Określa, że wyzwalacz DML uruchamia zamiast wyzwalacza instrukcji SQL, w związku z tym przesłania akcje instrukcji wyzwalania. Nie można określić INSTEAD OF wyzwalaczy DDL ani wyzwalaczy logowania.

Co najwyżej można zdefiniować jeden INSTEAD OF wyzwalacz na INSERTinstrukcję , UPDATElub DELETE w tabeli lub widoku. Można również zdefiniować widoki w widokach, w których każdy widok ma własny INSTEAD OF wyzwalacz.

Nie można definiować INSTEAD OF wyzwalaczy w widokach z możliwością aktualizacji, które używają polecenia WITH CHECK OPTION. Spowoduje to wystąpienie błędu po INSTEAD OF dodaniu wyzwalacza do określonego widoku WITH CHECK OPTION z możliwością aktualizacji. Tę opcję należy usunąć za pomocą polecenia ALTER VIEW przed zdefiniowaniem INSTEAD OF wyzwalacza.

{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ ] [ UPDATE ] }

Określa instrukcje modyfikacji danych, które aktywują wyzwalacz DML po wypróbowaniu tej tabeli lub widoku. Określ co najmniej jedną opcję. Użyj dowolnej kombinacji tych opcji w dowolnej kolejności w definicji wyzwalacza.

W przypadku INSTEAD OF wyzwalaczy nie można użyć DELETE opcji w tabelach, które mają relację odwołania, określając akcję ON DELETEkaskadową . UPDATE Podobnie opcja nie jest dozwolona w tabelach, które mają relację odwołania, określając akcję ON UPDATEkaskadową .

Z DOŁĄCZANIEM

Dotyczy: SQL Server 2008 (10.0.x) do programu SQL Server 2008 R2 (10.50.x).

Określa, że należy dodać dodatkowy wyzwalacz istniejącego typu. WITH APPEND Nie można używać z wyzwalaczami INSTEAD OF lub jeśli AFTER wyzwalacz jest jawnie określony. W przypadku zgodności z poprzednimi wersjami należy użyć WITH APPEND tylko wtedy, gdy FOR jest określony, bez INSTEAD OF lub AFTER. Nie można określić WITH APPEND , czy jest używany EXTERNAL NAME (oznacza to, że wyzwalacz jest wyzwalaczem CLR).

event_type

Nazwa zdarzenia języka Transact-SQL, które po uruchomieniu powoduje uruchomienie wyzwalacza DDL. Prawidłowe zdarzenia dla wyzwalaczy DDL są wymienione w zdarzeniach DDL.

event_group

Nazwa wstępnie zdefiniowanej grupy zdarzeń języka Transact-SQL. Wyzwalacz DDL jest uruchamiany po uruchomieniu dowolnego zdarzenia języka Transact-SQL, które należy do event_group. Prawidłowe grupy zdarzeń dla wyzwalaczy DDL są wymienione w grupach zdarzeń DDL.

Po zakończeniu CREATE TRIGGER działania event_group również działa jako makro, dodając typy zdarzeń, które obejmuje do sys.trigger_events widoku wykazu.

NIE DOTYCZY REPLIKACJI

Wskazuje, że wyzwalacz nie powinien być uruchamiany, gdy agent replikacji modyfikuje tabelę, która jest zaangażowana w wyzwalacz.

sql_statement

Warunki i akcje wyzwalacza. Warunki wyzwalacza określają dodatkowe kryteria określające, czy wypróbowane zdarzenia DML, DDL lub logowania powodują uruchomienie akcji wyzwalacza.

Akcje wyzwalacza określone w instrukcjach Transact-SQL wchodzą w życie po wypróbowaniu operacji.

Wyzwalacze mogą zawierać dowolną liczbę i typ instrukcji Transact-SQL z wyjątkami. Aby uzyskać więcej informacji zobacz uwagi. Wyzwalacz jest przeznaczony do sprawdzania lub zmieniania danych na podstawie instrukcji modyfikacji lub definicji danych. Wyzwalacz nie powinien zwracać danych do użytkownika. Instrukcje Transact-SQL w wyzwalaczu często obejmują język sterowania przepływem.

Wyzwalacze DML używają usuniętych i wstawionych tabel logicznych (koncepcyjnych). Są one strukturalnie podobne do tabeli, w której zdefiniowano wyzwalacz, czyli tabelę, na której jest podejmowana akcja użytkownika. Usunięte i wstawione tabele przechowują stare wartości lub nowe wartości wierszy, które mogą zostać zmienione przez akcję użytkownika. Aby na przykład pobrać wszystkie wartości w deleted tabeli, użyj:

SELECT * FROM deleted;

Aby uzyskać więcej informacji, zobacz Używanie wstawionych i usuniętych tabel.

Wyzwalacze DDL i logowania przechwytują informacje o zdarzeniu wyzwalania przy użyciu funkcji EVENTDATA . Aby uzyskać więcej informacji, zobacz Używanie funkcji EVENTDATA.

Program SQL Server umożliwia aktualizowanie kolumn tekstowych, ntekstowych lub obrazów za pośrednictwem INSTEAD OF wyzwalacza w tabelach lub widokach.

W przypadku wyzwalaczy w tabelach zoptymalizowanych pod kątem pamięci jedynym sql_statement dozwolonym ATOMIC na najwyższym poziomie jest blok. Język T-SQL dozwolony wewnątrz ATOMIC bloku jest ograniczony przez język T-SQL dozwolony wewnątrz natywnych procs.

<method_specifier>

W przypadku wyzwalacza CLR określa metodę zestawu do powiązania z wyzwalaczem. Metoda nie może przyjmować żadnych argumentów i zwracać wartość void. class_name musi być prawidłowym identyfikatorem programu SQL Server i musi istnieć jako klasa w zestawie z widocznością zestawu. Jeśli klasa ma kwalifikowaną nazwę przestrzeni nazw używaną . do oddzielania części przestrzeni nazw, nazwa klasy musi być rozdzielana przy użyciu ograniczników [ ] lub " . Klasa nie może być klasą zagnieżdżonych.

Uwagi dotyczące wyzwalaczy DML

Wyzwalacze DML są często używane do wymuszania reguł biznesowych i integralności danych. Program SQL Server zapewnia deklaratywną integralność referencyjną (DRI) za pomocą instrukcji ALTER TABLE i CREATE TABLE . Jednak funkcja DRI nie zapewnia integralności referencyjnej między bazami danych. Integralność referencyjna odnosi się do reguł dotyczących relacji między kluczami podstawowymi i obcymi tabel. Aby wymusić integralność referencyjną, użyj PRIMARY KEY ograniczeń i FOREIGN KEY w ALTER TABLE systemach i CREATE TABLE. Jeśli ograniczenia istnieją w tabeli wyzwalacza, są sprawdzane po uruchomieniu INSTEAD OF wyzwalacza i przed uruchomieniem AFTER wyzwalacza. Jeśli ograniczenia zostaną naruszone, INSTEAD OF akcje wyzwalacza zostaną wycofane, a AFTER wyzwalacz nie zostanie wyzwolony.

Za pomocą polecenia sp_settriggerordermożna określić pierwsze i ostatnie AFTER wyzwalacze, które mają być uruchamiane w tabeli. Można określić tylko jeden pierwszy i jeden ostatni AFTER wyzwalacz dla każdej INSERToperacji , UPDATEi DELETE w tabeli. Jeśli w tej samej tabeli znajdują się inne AFTER wyzwalacze, są one uruchamiane losowo.

ALTER TRIGGER Jeśli instrukcja zmieni pierwszy lub ostatni wyzwalacz, pierwszy lub ostatni atrybut ustawiony na zmodyfikowany wyzwalacz zostanie porzucony i należy zresetować wartość zamówienia przy użyciu polecenia sp_settriggerorder.

Wyzwalacz AFTER jest uruchamiany dopiero po pomyślnym uruchomieniu instrukcji SQL wyzwalającej. To pomyślne wykonanie obejmuje wszystkie akcje kaskadowe odwołań i kontrole ograniczeń skojarzone z obiektem zaktualizowanym lub usuniętym. Element AFTER nie rekursywnie uruchamia INSTEAD OF wyzwalacza w tej samej tabeli.

INSTEAD OF Jeśli wyzwalacz zdefiniowany w tabeli uruchamia instrukcję względem tabeli, która zwykle INSTEAD OF uruchamia wyzwalacz ponownie, wyzwalacz nie jest wywoływany rekursywnie. Zamiast tego instrukcja przetwarza tak, jakby tabela nie INSTEAD OF miała wyzwalacza i uruchamia łańcuch operacji ograniczeń i AFTER wykonań wyzwalacza. Jeśli na przykład wyzwalacz jest zdefiniowany jako INSTEAD OF INSERT wyzwalacz dla tabeli. Jeśli wyzwalacz uruchamia instrukcję INSERT w tej samej tabeli, INSERT instrukcja uruchomiona przez INSTEAD OF wyzwalacz nie wywołuje ponownie wyzwalacza. Wyzwalacz INSERT uruchamiany przez wyzwalacz rozpoczyna proces uruchamiania akcji ograniczeń i wyzwalania wszystkich AFTER INSERT wyzwalaczy zdefiniowanych dla tabeli.

INSTEAD OF Gdy wyzwalacz zdefiniowany w widoku uruchamia instrukcję w widoku, która zwykle INSTEAD OF uruchamia wyzwalacz ponownie, nie jest wywoływana rekursywnie. Zamiast tego instrukcja jest rozpoznawana jako modyfikacja tabel bazowych w widoku. W takim przypadku definicja widoku musi spełniać wszystkie ograniczenia dotyczące widoku z możliwością aktualizacji. Aby uzyskać definicję widoków z możliwością aktualizowania, zobacz Modyfikowanie danych za pośrednictwem widoku.

Jeśli na przykład wyzwalacz jest zdefiniowany jako INSTEAD OF UPDATE wyzwalacz dla widoku. Wyzwalacz uruchamia instrukcję odwołującą UPDATE się do tego samego widoku. Instrukcja UPDATE uruchamiana przez INSTEAD OF wyzwalacz nie wywołuje ponownie wyzwalacza. Wyzwalacz UPDATE uruchamiany przez wyzwalacz jest przetwarzany w widoku tak, jakby widok nie miał INSTEAD OF wyzwalacza. Kolumny zmienione przez UPDATE element muszą być rozpoznawane jako jedna tabela podstawowa. Każda modyfikacja podstawowej tabeli podstawowej rozpoczyna łańcuch stosowania ograniczeń i wyzwalaczy wyzwalaczy AFTER zdefiniowanych dla tabeli.

Testowanie akcji UPDATE lub INSERT dla określonych kolumn

Wyzwalacz Transact-SQL można zaprojektować, aby wykonać określone akcje na UPDATE podstawie określonych kolumn lub INSERT zmodyfikować je. W tym celu użyj polecenia UPDATE lub COLUMNS_UPDATED w treści wyzwalacza. UPDATE() testy dla UPDATE jednej kolumny lub INSERT próby. COLUMNS_UPDATED testy dla UPDATE lub INSERT akcji uruchamianych w wielu kolumnach. Ta funkcja zwraca wzorzec bitowy wskazujący, które kolumny zostały wstawione lub zaktualizowane.

Ograniczenia wyzwalacza

CREATE TRIGGER musi być pierwszą instrukcją w partii i może być stosowana tylko do jednej tabeli.

Wyzwalacz jest tworzony tylko w bieżącej bazie danych; wyzwalacz może jednak odwoływać się do obiektów spoza bieżącej bazy danych.

Jeśli nazwa schematu wyzwalacza jest określona w celu zakwalifikowania wyzwalacza, należy zakwalifikować nazwę tabeli w taki sam sposób.

Tę samą akcję wyzwalacza można zdefiniować dla więcej niż jednej akcji użytkownika (na przykład INSERT i UPDATE) w tej samej CREATE TRIGGER instrukcji.

INSTEAD OF DELETE / INSTEAD OF UPDATE wyzwalaczy nie można zdefiniować w tabeli, która ma klucz obcy ze zdefiniowaną kaskadą DELETE/UPDATE akcji.

Dowolną instrukcję SET można określić wewnątrz wyzwalacza. Wybrana opcja SET pozostaje w mocy podczas wykonywania wyzwalacza, a następnie przywraca jej poprzednie ustawienie.

Po wyzwoleniu wyzwalacza wyniki są zwracane do aplikacji wywołującej, podobnie jak w przypadku procedur składowanych. Aby zapobiec zwracaniu wyników do aplikacji z powodu wyzwalacza wyzwalacza, nie dołączaj SELECT instrukcji, które zwracają wyniki lub instrukcje, które wykonują przypisanie zmiennych w wyzwalaczu. Wyzwalacz zawierający SELECT instrukcje zwracające wyniki do użytkownika lub instrukcji, które wykonują przypisanie zmiennej, wymaga specjalnej obsługi. Należy zapisać zwrócone wyniki w każdej aplikacji, w której dozwolone są modyfikacje tabeli wyzwalaczy. Jeśli przypisanie zmiennej musi wystąpić w wyzwalaczu, użyj SET NOCOUNT instrukcji na początku wyzwalacza, aby zapobiec zwracaniu dowolnych zestawów wyników.

TRUNCATE TABLE Mimo że instrukcja jest w mocy instrukcjiDELETE, nie aktywuje wyzwalacza, ponieważ operacja nie rejestruje pojedynczych usunięć wierszy. Jednak tylko ci użytkownicy z uprawnieniami do uruchamiania TRUNCATE TABLE instrukcji muszą być zaniepokojeni przypadkowo obejściem DELETE wyzwalacza w ten sposób.

Instrukcja WRITETEXT , niezależnie od tego, czy jest rejestrowana, czy nie jest włączona, nie aktywuje wyzwalacza.

Następujące instrukcje Transact-SQL nie są dozwolone w wyzwalaczu DML:

• ALTER DATABASE
• CREATE DATABASE
• DROP DATABASE
• RESTORE DATABASE
• RESTORE LOG
• RECONFIGURE

Ponadto następujące instrukcje Transact-SQL nie są dozwolone wewnątrz treści wyzwalacza DML, gdy są używane względem tabeli lub widoku docelowego akcji wyzwalającej.

• CREATE INDEX (w tym CREATE SPATIAL INDEX i CREATE XML INDEX)
• ALTER INDEX
• DROP INDEX
• DROP TABLE
• DBCC DBREINDEX
• ALTER PARTITION FUNCTION
• ALTER TABLE w przypadku użycia do wykonywania następujących akcji:
  • Dodaj, zmodyfikuj lub upuść kolumny.
  • Przełączanie partycji.
  • Dodaj lub upuść PRIMARY KEY lub UNIQUE ograniczenia.

Optymalizowanie wyzwalaczy DML

Wyzwalacze działają w transakcjach (implikowane lub w inny sposób) i gdy są otwarte, blokują zasoby. Blokada pozostaje w miejscu, dopóki transakcja nie zostanie potwierdzona (z COMMIT) lub odrzucona (z ROLLBACK). Im dłużej wyzwalacz jest uruchamiany, tym większe prawdopodobieństwo, że inny proces jest następnie blokowany. Dlatego wyzwalacze zapisu, aby zmniejszyć ich czas trwania zawsze, gdy jest to możliwe. Jednym ze sposobów osiągnięcia krótszego czasu trwania jest zwolnienie wyzwalacza, gdy instrukcja DML zmienia zero wierszy.

Aby zwolnić wyzwalacz dla polecenia, które nie zmienia żadnych wierszy, należy stosować zmienną systemową ROWCOUNT_BIG.

Poniższy fragment kodu języka T-SQL pokazuje, jak zwolnić wyzwalacz dla polecenia, które nie zmienia żadnych wierszy. Ten kod powinien być obecny na początku każdego wyzwalacza DML:

IF (ROWCOUNT_BIG() = 0)
RETURN;

Uwagi dotyczące wyzwalaczy DDL

Wyzwalacze DDL, takie jak standardowe wyzwalacze, uruchamiają procedury składowane w odpowiedzi na zdarzenie. Jednak w przeciwieństwie do standardowych wyzwalaczy nie są one uruchamiane w odpowiedzi na UPDATEinstrukcje , INSERTlub DELETE w tabeli lub widoku. Zamiast tego są one uruchamiane głównie w odpowiedzi na instrukcje języka definicji danych (DDL). Typy instrukcji obejmują CREATE, , ALTER, GRANTDROP, DENY, REVOKE, i UPDATE STATISTICS. Niektóre systemowe procedury składowane, które wykonują operacje podobne do języka DDL, mogą również uruchamiać wyzwalacze DDL.

Aby uzyskać więcej informacji na temat wyzwalaczy DDL, zobacz Wyzwalacze DDL.

Wyzwalacze DDL nie są wyzwalane w odpowiedzi na zdarzenia wpływające na lokalne lub globalne tabele tymczasowe i procedury składowane.

W przeciwieństwie do wyzwalaczy DML wyzwalacze DDL nie są ograniczone do schematów. Dlatego nie można używać funkcji, takich jak OBJECT_ID, OBJECT_NAME, OBJECTPROPERTYi OBJECTPROPERTYEX do wykonywania zapytań dotyczących metadanych dotyczących wyzwalaczy DDL. Zamiast tego użyj widoków wykazu. Aby uzyskać więcej informacji, zobacz Uzyskiwanie informacji o wyzwalaczach DDL.

Wyzwalacze logowania

Wyzwalacze logowania wykonują procedury składowane w odpowiedzi na LOGON zdarzenie. To zdarzenie występuje, gdy sesja użytkownika jest ustanawiana z wystąpieniem programu SQL Server. Wyzwalanie logowania jest wyzwalane po zakończeniu fazy uwierzytelniania logowania, ale przed nawiązaniem sesji użytkownika. Dlatego wszystkie komunikaty pochodzące wewnątrz wyzwalacza, które zwykle docierają do użytkownika, takie jak komunikaty o błędach i komunikaty z PRINT instrukcji, są przekierowywane do dziennika błędów programu SQL Server. Aby uzyskać więcej informacji, zobacz Wyzwalacze logowania.

Wyzwalacze logowania nie są wyzwalane, jeśli uwierzytelnianie zakończy się niepowodzeniem.

Transakcje rozproszone nie są obsługiwane w wyzwalaczu logowania. Błąd 3969 jest zwracany, gdy wyzwalacz logowania zawierający rozproszony wyzwalacz transakcji.

Wyłączanie wyzwalacza logowania

Wyzwalacz logowania może skutecznie zapobiec pomyślnym połączeniom z aparatem bazy danych dla wszystkich użytkowników, w tym członków stałej roli serwera sysadmin . Gdy wyzwalacz logowania uniemożliwia nawiązywanie połączeń, członkowie stałej roli serwera sysadmin mogą łączyć się przy użyciu dedykowanego połączenia administratora lub uruchamiając aparat bazy danych w trybie minimalnej konfiguracji (-f). Aby uzyskać więcej informacji, zapoznaj się z opcjami uruchamiania usługi silnika bazy danych .

Ogólne zagadnienia dotyczące wyzwalacza

Zwracanie wyników

Możliwość zwracania wyników z wyzwalaczy zostanie usunięta w przyszłej wersji programu SQL Server. Wyzwalacze zwracające zestawy wyników mogą powodować nieoczekiwane zachowanie w aplikacjach, które nie są przeznaczone do pracy z nimi. Unikaj zwracania zestawów wyników z wyzwalaczy w nowych pracach programistycznych i zaplanuj modyfikowanie aplikacji, które są obecnie wykonywane. Aby zapobiec zwracaniu zestawów wyników przez wyzwalacze, ustaw opcję Nie zezwalaj na wyniki z wyzwalaczy na 1.

Wyzwalacze logowania zawsze nie zezwalają na zwracanie zestawów wyników, a to zachowanie nie jest konfigurowalne. Jeśli wyzwalacz logowania generuje zestaw wyników, uruchomienie wyzwalacza nie powiedzie się i próba zalogowania, która wyzwoliła wyzwalacz, zostanie odrzucona.

Wiele wyzwalaczy

Program SQL Server umożliwia tworzenie wielu wyzwalaczy dla każdego zdarzenia DML, DDL lub LOGON DDL. Jeśli na przykład CREATE TRIGGER FOR UPDATE jest uruchamiana dla tabeli, która ma UPDATE już wyzwalacz, zostanie utworzony dodatkowy wyzwalacz aktualizacji. We wcześniejszych wersjach programu SQL Server dozwolone jest tylko jedno wyzwalacze dla każdego INSERTzdarzenia modyfikacji danych lub DELETE , UPDATEdla każdej tabeli.

Wyzwalacze cykliczne

Program SQL Server obsługuje również cykliczne wywołanie wyzwalaczy, gdy RECURSIVE_TRIGGERS ustawienie jest włączone przy użyciu polecenia ALTER DATABASE.

Wyzwalacze rekursywne umożliwiają występowanie następujących typów rekursji:

• Rekursja pośrednia: w przypadku rekursji pośredniej aplikacja aktualizuje tabelę T1. Spowoduje to wyzwolenie wyzwalacza TR1, aktualizowanie tabeli T2. Następnie wyzwalacz T2 uruchamia i aktualizuje tabelę T1.

• Rekursja bezpośrednia: w rekursji bezpośredniej aplikacja aktualizuje tabelę T1. Spowoduje to wyzwolenie wyzwalacza TR1, aktualizowanie tabeli T1. Ponieważ tabela T1 została zaktualizowana, wyzwalacz TR1 jest uruchamiany ponownie itd.

W poniższym przykładzie użyto rekursji wyzwalacza pośredniego i bezpośredniego Przyjmij, TR1 że dwa wyzwalacze aktualizacji i TR2, są zdefiniowane w tabeli T1. Wyzwalanie TR1 tabeli T1 aktualizacji cyklicznie. Instrukcja UPDATE jest uruchamiana za każdym TR1TR2 razem. Ponadto uruchomienie TR1 wyzwalaczy wykonywania TR1 (rekursywnie) i TR2. Wstawione i usunięte tabele dla określonego wyzwalacza zawierają wiersze odpowiadające tylko UPDATE instrukcji, która wywołała wyzwalacz.

RECURSIVE_TRIGGERS Wyłączenie ustawienia uniemożliwia tylko bezpośrednie rekursje. Aby wyłączyć rekursję pośrednią, ustaw opcję serwera wyzwalaczy zagnieżdżonych na wartość 0 przy użyciu polecenia sp_configure.

Jeśli którykolwiek z wyzwalaczy wykonuje ROLLBACK TRANSACTIONwartość , niezależnie od poziomu zagnieżdżenia, nie są uruchamiane żadne więcej wyzwalaczy.

Wyzwalacze zagnieżdżone

Wyzwalacze można zagnieżdżać do maksymalnie 32 poziomów. Jeśli wyzwalacz zmieni tabelę, w której istnieje inny wyzwalacz, drugi wyzwalacz aktywuje się i może wywołać trzeci wyzwalacz itd. Jeśli jakikolwiek wyzwalacz w łańcuchu uruchamia nieskończoną pętlę, poziom zagnieżdżania zostanie przekroczony, a wyzwalacz zostanie anulowany. Gdy wyzwalacz Transact-SQL uruchamia zarządzany kod, odwołując się do procedury, typu lub agregacji CLR, to odwołanie liczy się jako jeden poziom względem limitu zagnieżdżenia na poziomie 32. Metody wywoływane z poziomu kodu zarządzanego nie są liczone względem tego limitu.

Aby wyłączyć zagnieżdżone wyzwalacze, ustaw opcję zagnieżdżonych wyzwalaczy na sp_configure wartość 0 (wyłączone). Domyślna konfiguracja obsługuje zagnieżdżone wyzwalacze. Jeśli zagnieżdżone wyzwalacze są wyłączone, wyzwalacze cykliczne są również wyłączone, pomimo ustawienia ustawionego RECURSIVE_TRIGGERS za pomocą polecenia ALTER DATABASE.

Pierwszy AFTER wyzwalacz zagnieżdżony wewnątrz INSTEAD OF wyzwalacza jest uruchamiany nawet wtedy, gdy zagnieżdżona opcja konfiguracji serwera to 0. Jednak w ramach tego ustawienia późniejsze AFTER wyzwalacze nie są uruchamiane. Przejrzyj aplikacje dla zagnieżdżonych wyzwalaczy, aby określić, czy aplikacje są zgodne z regułami biznesowymi, gdy zagnieżdżona opcja konfiguracji serwera jest ustawiona na 0. Jeśli nie, wprowadź odpowiednie modyfikacje.

Odroczone rozpoznawanie nazw

Program SQL Server umożliwia Transact-SQL procedur składowanych, wyzwalaczy, funkcji i partii w celu odwoływania się do tabel, które nie istnieją w czasie kompilacji. Ta możliwość jest nazywana odroczonym rozpoznawaniem nazw.

Uprawnienia

Aby utworzyć wyzwalacz DML, wymaga ALTER uprawnień do tabeli lub widoku, w którym jest tworzony wyzwalacz.

Aby utworzyć wyzwalacz DDL z zakresem serwera (ON ALL SERVER) lub wyzwalaczem logowania, wymaga CONTROL SERVER uprawnień na serwerze. Aby utworzyć wyzwalacz DDL z zakresem bazy danych (ON DATABASE), wymaga ALTER ANY DATABASE DDL TRIGGER uprawnień w bieżącej bazie danych.

Przykłady

Odp. Używanie wyzwalacza DML z komunikatem z przypomnieniem

Poniższy wyzwalacz DML wyświetla komunikat do klienta, gdy ktoś próbuje dodać lub zmienić dane w Customer tabeli w bazie danych AdventureWorks2022.

CREATE TRIGGER reminder1
ON Sales.Customer
AFTER INSERT, UPDATE
AS RAISERROR ('Notify Customer Relations', 16, 10);
GO

B. Używanie wyzwalacza DML z wiadomością e-mail z przypomnieniem

Poniższy przykład wysyła wiadomość e-mail do określonej osoby (MaryM) po Customer zmianie tabeli.

CREATE TRIGGER reminder2
ON Sales.Customer
AFTER INSERT, UPDATE, DELETE
AS
    EXECUTE msdb.dbo.sp_send_dbmail
        @profile_name = 'AdventureWorks2022 Administrator',
        @recipients = 'danw@Adventure-Works.com',
        @body = 'Don''t forget to print a report for the sales force.',
        @subject = 'Reminder';
GO

C. Użyj wyzwalacza DML AFTER, aby wymusić regułę biznesową między tabelami PurchaseOrderHeader i Vendor

Ponieważ CHECK ograniczenia odwołują się tylko do kolumn, na których zdefiniowano ograniczenie na poziomie kolumny lub na poziomie tabeli, należy zdefiniować wszelkie ograniczenia między tabelami (w tym przypadku reguły biznesowe) jako wyzwalacze.

Poniższy przykład tworzy wyzwalacz DML w AdventureWorks2022 bazie danych. Ten wyzwalacz sprawdza, czy ocena kredytowa dostawcy jest dobra (a nie 5), gdy jest podejmowana próba wstawienia nowego zamówienia zakupu do PurchaseOrderHeader tabeli. Aby uzyskać ocenę kredytową dostawcy, Vendor należy odwołać się do tabeli. Jeśli ocena kredytowa jest zbyt niska, zostanie wyświetlony komunikat i nie nastąpi wstawienie.

USE AdventureWorks2022;
GO

IF OBJECT_ID('Purchasing.LowCredit', 'TR') IS NOT NULL
    DROP TRIGGER Purchasing.LowCredit;
GO

-- This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table
-- when the credit rating of the specified vendor is set to 5 (below average).
CREATE TRIGGER Purchasing.LowCredit
ON Purchasing.PurchaseOrderHeader
AFTER INSERT
AS
    IF (ROWCOUNT_BIG() = 0)
    RETURN;
    IF EXISTS (SELECT 1
        FROM inserted AS i
        INNER JOIN Purchasing.Vendor AS v
            ON v.BusinessEntityID = i.VendorID
            WHERE v.CreditRating = 5)
BEGIN
    RAISERROR ('A vendor''s credit rating is too low to accept new purchase orders.', 16, 1);
    ROLLBACK;
    RETURN;
END
GO

-- This statement attempts to insert a row into the PurchaseOrderHeader table  
-- for a vendor that has a below average credit rating.  
-- The AFTER INSERT trigger is fired and the INSERT transaction is rolled back.  

INSERT INTO Purchasing.PurchaseOrderHeader (RevisionNumber, Status, EmployeeID,
    VendorID, ShipMethodID, OrderDate, ShipDate, SubTotal, TaxAmt, Freight)
VALUES (2, 3, 261, 1652, 4, GETDATE(), GETDATE(), 44594.55, 3567.564, 1114.8638);
GO

D. Używanie wyzwalacza DDL o zakresie bazy danych

W poniższym przykładzie użyto wyzwalacza DDL, aby uniemożliwić usunięcie jakiegokolwiek synonimu w bazie danych.

CREATE TRIGGER safety
    ON DATABASE
    FOR DROP_SYNONYM
    AS IF (@@ROWCOUNT = 0)
           RETURN;
       RAISERROR ('You must disable Trigger "safety" to remove synonyms!', 10, 1);
       ROLLBACK;
GO

DROP TRIGGER safety
    ON DATABASE;
GO

E. Używanie wyzwalacza DDL o zakresie serwera

W poniższym przykładzie użyto wyzwalacza DDL, aby wydrukować komunikat, jeśli jakiekolwiek CREATE DATABASE zdarzenie wystąpi w bieżącym wystąpieniu serwera i używa EVENTDATA funkcji do pobrania tekstu odpowiadającej instrukcji Transact-SQL. Aby uzyskać więcej przykładów używanych EVENTDATA w wyzwalaczach DDL, zobacz Używanie funkcji EVENTDATA.

CREATE TRIGGER ddl_trig_database
    ON ALL SERVER
    FOR CREATE_DATABASE
    AS PRINT 'Database Created.';
       SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)');
GO

DROP TRIGGER ddl_trig_database
    ON ALL SERVER;
GO

F. Używanie wyzwalacza logowania

Poniższy przykład wyzwalacza logowania uniemożliwia próbę zalogowania się do programu SQL Server jako członka login_test logowania, jeśli istnieją już trzy sesje użytkownika uruchomione w ramach tego logowania. Zmień <password> na silne hasło.

USE master;
GO

CREATE LOGIN login_test
    WITH PASSWORD = '<password>' MUST_CHANGE, CHECK_EXPIRATION = ON;
GO

GRANT VIEW SERVER STATE TO login_test;
GO

CREATE TRIGGER connection_limit_trigger
    ON ALL SERVER
    WITH EXECUTE AS 'login_test'
    FOR LOGON
    AS BEGIN
           IF ORIGINAL_LOGIN() = 'login_test'
              AND (SELECT COUNT(*)
                   FROM sys.dm_exec_sessions
                   WHERE is_user_process = 1
                         AND original_login_name = 'login_test') > 3
               ROLLBACK;
       END

G. Wyświetlanie zdarzeń, które powodują wyzwolenie wyzwalacza

Poniższy przykład wysyła zapytania do sys.triggers widoków i sys.trigger_events wykazu, aby określić, które zdarzenia języka Transact-SQL powodują wyzwolenie wyzwalacza safety . Wyzwalacz safety, jest tworzony w przykładzie D. Użyj wyzwalacza DDL o zakresie bazy danych.

SELECT TE.*
FROM sys.trigger_events AS TE
     INNER JOIN sys.triggers AS T
         ON T.object_id = TE.object_id
WHERE T.parent_class = 0
      AND T.name = 'safety';
GO

Treści powiązane

• ALTER TABLE (Transact-SQL)
• ALTER TRIGGER (Transact-SQL)
• COLUMNS_UPDATED (Transact-SQL)
• CREATE TABLE (Transact-SQL)
• WYZWALACZ DROP (Transact-SQL)
• WŁĄCZ WYZWALACZ (Transact-SQL)
• WYŁĄCZ WYZWALACZ (Transact-SQL)
• TRIGGER_NESTLEVEL (Transact-SQL)
• EVENTDATA (Transact-SQL)
• sys.dm_sql_referenced_entities
• sys.dm_sql_referencing_entities
• sys.sql_expression_dependencies
• sp_help
• sp_helptrigger
• sp_helptext
• sp_rename
• sp_settriggerorder
• UPDATE — funkcje wyzwalacza (Transact-SQL)
• Uzyskaj informacje o wyzwalaczach DML
• Uzyskiwanie informacji o wyzwalaczach DDL
• sys.triggers
• sys.trigger_events
• sys.sql_modules
• sys.assembly_modules
• sys.server_triggers
• sys.server_trigger_events
• sys.server_sql_modules
• sys.server_assembly_modules