CREATE TRIGGER (Transact-SQL)

  • 10/30/2019
  • 24 minutos para ler
  • >ul>
  • W
  • M
  • c
  • r
  • m
  • +16

/li> /li>

P>Candidata-se a: yesSQL Server (todas as versões suportadas) SimAzure SQL Database

Cria um DML, DDL, ou gatilho de logon. Um gatilho é um tipo especial de procedimento armazenado que corre automaticamente quando um evento ocorre no servidor da base de dados. O DML dispara quando um utilizador tenta modificar dados através de um evento de linguagem de manipulação de dados (DML). Os eventos DML são declarações INSERT, UPDATE, ou DELETE sobre uma tabela ou vista. Estes disparam quando qualquer evento válido dispara, quer as linhas da tabela sejam afectadas ou não. Para mais informações, ver Gatilhos DML.

DDL dispara em resposta a uma variedade de eventos de linguagem de definição de dados (DDL). Estes eventos correspondem principalmente a declarações Transact-SQL CREATE, ALTER, e DROP, e a certos procedimentos armazenados no sistema que executam operações do tipo DDL.

Logon dispara o fogo em resposta ao evento LOGON que é levantado quando uma sessão do utilizador está a ser estabelecida. Pode-se criar disparos directamente a partir de instruções Transact-SQL ou de métodos de conjuntos que são criados no Microsoft .NET Framework common language runtime (CLR) e carregados para uma instância do SQL Server. SQL Server permite-lhe criar múltiplos triggers para qualquer instrução específica.

Importante

Código malicioso dentro de triggers pode ser executado sob privilégios escalonados. Para mais informações sobre como mitigar esta ameaça, ver Manage Trigger Security.

Nota

A integração de .NET Framework CLR no SQL Server é discutida neste artigo. A integração de CLR não se aplica à base de dados Azure SQL.

Icone de ligação tópica Transact-Convenções de Sintaxe SQL

Sintaxe do Servidor SQL

-- SQL Server Syntax -- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger) CREATE TRIGGER trigger_name ON { table | view } ] { FOR | AFTER | INSTEAD OF } { } AS { sql_statement | EXTERNAL NAME <method specifier > } <dml_trigger_option> ::= <method_specifier> ::= assembly_name.class_name.method_name 
-- SQL Server Syntax -- Trigger on an INSERT, UPDATE, or DELETE statement to a -- table (DML Trigger on memory-optimized tables) CREATE TRIGGER trigger_name ON { table } ] { FOR | AFTER } { } AS { sql_statement } <dml_trigger_option> ::= 
-- Trigger on a CREATE, ALTER, DROP, GRANT, DENY, -- REVOKE or UPDATE statement (DDL Trigger) CREATE TRIGGER trigger_name ON { ALL SERVER | DATABASE } ] { FOR | AFTER } { event_type | event_group } AS { sql_statement | EXTERNAL NAME < method specifier > } <ddl_trigger_option> ::= 

-- Trigger on a LOGON event (Logon Trigger) CREATE TRIGGER trigger_name ON ALL SERVER ] { FOR| AFTER } LOGON AS { sql_statement | EXTERNAL NAME < method specifier > } <logon_trigger_option> ::= 

Azure SQL Database Syntax

-- Azure SQL Database Syntax -- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger) CREATE TRIGGER trigger_name ON { table | view } ] { FOR | AFTER | INSTEAD OF } { } AS { sql_statement > } <dml_trigger_option> ::= 
-- Azure SQL Database Syntax -- Trigger on a CREATE, ALTER, DROP, GRANT, DENY, -- REVOKE, or UPDATE STATISTICS statement (DDL Trigger) CREATE TRIGGER trigger_name ON { DATABASE } ] { FOR | AFTER } { event_type | event_group } AS { sql_statement } <ddl_trigger_option> ::= 

Nota

Para ver Transact-Sintaxe SQL para o SQL Server 2014 e anteriores, ver a documentação das versões anteriores.

Argumentos

OR ALTERAÇÃO
Candidaturas a: Azure SQL Database, SQL Server (começando com SQL Server 2016 (13.x) SP1).

Altera condicionalmente o gatilho apenas se já existir.

nome_do_schema
O nome do esquema ao qual pertence um gatilho DML. Os gatilhos de DML são delimitados ao esquema da tabela ou vista sobre a qual são criados. schema_name não pode ser especificado para DDL ou gatilhos de logon.

trigger_name
O nome do gatilho. Um trigger_name deve seguir as regras para identificadores, excepto que o trigger_name não pode começar com # ou ##.

tabela | view
a tabela ou vista sobre a qual o trigger DML é executado. Esta tabela ou vista é por vezes referida como tabela ou vista de gatilho. Especificar o nome completo qualificado da tabela ou da vista é opcional. Só se pode fazer referência a uma vista através de um INSTEAD OF trigger. Não é possível definir gatilhos DML em tabelas temporárias locais ou globais.

DATABASE
Aplica o âmbito de um gatilho DDL à base de dados actual. Se especificado, o gatilho dispara sempre que o tipo_de_evento ou grupo_de_evento ocorre na base de dados actual.

Todos os SERVIDORES
Aplica a: SQL Server 2008 e posteriores.

Aplica o âmbito de um gatilho DDL ou logon ao servidor actual. Se especificado, o gatilho dispara sempre que o tipo_de_evento ou grupo_de_evento ocorre em qualquer parte do servidor actual.

COM ENCRYPTION
Aplica a: SQL Server 2008 e posteriores.

Aplica a: SQL Server 2008 e posteriores.

Aplica o âmbito de um DDL ou gatilho de logon ao servidor actual: SQL Server 2008 e posteriores.

Obscura o texto da declaração CREATE TRIGGER. Usando COM ENCRYPTION impede que o gatilho seja publicado como parte da replicação do SQL Server. COM ENCRYPTION não pode ser especificado para gatilhos CLR.

EXECUTAR COMO
Especificar o contexto de segurança sob o qual o gatilho é executado. Permite controlar que conta de utilizador a instância do SQL Server utiliza para validar permissões em quaisquer objectos da base de dados que são referenciados pelo trigger.

Esta opção é necessária para triggers em tabelas optimizadas por memória.

Para mais informações, verEXECUTE AS Clause (Transact-SQL).

NATIVE_COMPILATION
Indica que o trigger é compilado nativamente.

Esta opção é necessária para triggers em tabelas optimizadas para memória.

SCHEMABINDING
Segura que as tabelas referenciadas por um trigger não podem ser largadas ou alteradas.

Esta opção é necessária para triggers em tabelas optimizadas para memória e não é suportada para triggers em tabelas tradicionais.

POR | DEPOIS
POR ou DEPOIS especifica que o trigger DML dispara apenas quando todas as operações especificadas na instrução SQL de triggering tiverem sido lançadas com sucesso. Todas as acções em cascata referenciais e verificações de restrição devem também ser bem sucedidas antes do disparo desta instrução de disparo.

Não é possível definir AFTER triggers nas vistas.

INSTEAD OF
Especifica que o disparo DML dispara em vez da instrução SQL de disparo, anulando assim as acções das instruções de disparo. Não é possível especificar INSTEAD OF para DDL ou triggers de logon.

No máximo, é possível definir uma instrução INSTEAD OF por INSERT, UPDATE, ou DELETE numa tabela ou numa vista. Também pode definir vistas em vistas onde cada vista tem o seu próprio INSTEAD OF trigger.

Não pode definir INSTEAD OF triggers em vistas actualizáveis que usam COM OPÇÃO DE VERIFICAÇÃO. Ao fazê-lo resulta num erro quando um INSTEAD OF trigger é adicionado a uma vista actualizável com a opção CHECK OPTION especificada. Remova essa opção usando ALTER VIEW antes de definir o INSTEAD OF trigger.

{ }
Escreve as declarações de modificação de dados que activam o gatilho DML quando este é tentado contra esta tabela ou vista. Especifica pelo menos uma opção. Use qualquer combinação destas opções em qualquer ordem na definição de trigger.

Para INSTEAD OF triggers, não pode usar a opção DELETE em tabelas que tenham uma relação referencial, especificando uma acção em cascata ON DELETE. Da mesma forma, a opção UPDATE não é permitida em tabelas que têm uma relação referencial, especificando uma acção em cascata ON UPDATE.

WITH APPEND
Applies to: SQL Server 2008 através do SQL Server 2008 R2.

Especifica que deve ser adicionado um gatilho adicional de um tipo existente. COM APÊNDICE não pode ser utilizado com INSTEAD OF triggers ou se um AFTER trigger for explicitamente declarado. Para compatibilidade retroactiva, utilizar COM APÊNDICE apenas quando FOR especificado, sem INSTEAD OF ou DEPOIS. Não se pode especificar COM APÊNDICE se usar NOME EXTERNO (isto é, se o gatilho for um gatilho CLR).

event_type
O nome de um evento em linguagem Transact-SQL que, após o lançamento, provoca o disparo de um gatilho DDL. Os eventos válidos para gatilhos DDL são listados em Eventos DDL.

event_group
O nome de um agrupamento predefinido de eventos em linguagem Transact-SQL. O gatilho DDL dispara após o lançamento de qualquer evento em linguagem Transact-SQL que pertença ao grupo event_group. Grupos de eventos válidos para gatilhos DDL são listados em DDL Event Groups.

Após o CREATE TRIGGER ter terminado de funcionar, event_group também actua como macro ao adicionar os tipos de eventos que cobre à visualização do catálogo sys.trigger_events.

NOT FOR REPLICATION
Applies to: SQL Server 2008 e posteriores.

Indica que o trigger não deve ser executado quando um agente de replicação modifica a tabela que está envolvida no trigger.

sql_statement
As condições e acções do trigger. As condições de disparo especificam critérios adicionais que determinam se os eventos DML, DDL, ou logon experimentados causam a execução das acções de disparo.

As acções de disparo especificadas nas instruções Transact-SQL entram em vigor quando a operação é experimentada.

Os disparadores podem incluir qualquer número e tipo de instruções Transact-SQL, com excepções. Para mais informações, ver Observações. Um disparador é concebido para verificar ou alterar dados com base numa declaração de modificação ou definição de dados; não deve devolver dados ao utilizador. As instruções Transact-SQL num trigger incluem frequentemente a linguagem de controlo de fluxo.

DML triggers utilizam as tabelas lógicas (conceptuais) apagadas e inseridas. São estruturalmente semelhantes à tabela sobre a qual o gatilho é definido, ou seja, a tabela sobre a qual a acção do utilizador é tentada. As tabelas apagadas e inseridas contêm os valores antigos ou novos valores das linhas que podem ser alterados pela acção do utilizador. Por exemplo, para recuperar todos os valores na tabela deleted, use:

SELECT * FROM deleted; 

Para mais informações, consulte Usar as Tabelas inseridas e apagadas.

DDL e o logon dispara a captura de informação sobre o evento de disparo usando a função EVENTDATA (Transact-SQL). Para mais informações, ver Utilizar a função EVENTDATA.

SQL Server permite a actualização de colunas de texto, ntext, ou imagem através do INSTEAD OF trigger em tabelas ou vistas.

Important

ntext, texto, e tipos de dados de imagem serão removidos numa futura versão do MicrosoftSQL Server. Evitar a utilização destes tipos de dados em novos trabalhos de desenvolvimento, e planear modificar as aplicações que actualmente os utilizam. Utilize antes nvarchar(max), varchar(max), e varbinary(max). Tanto o AFTER como o INSTEAD OF triggers suportam dados varchar(MAX), nvarchar(MAX), e varbinary(MAX) nas tabelas inseridas e apagadas.

Para triggers em tabelas optimizadas por memória, o único sql_statement permitido no nível superior é um bloco ATOMIC. O T-SQL permitido dentro do bloco ATOMIC é limitado pelo T-SQL permitido dentro de procs.

< method_specifier >Applies to: SQL Server 2008 e posteriores.

Para um disparador CLR, especifica o método de um conjunto a ligar com o disparador. O método não deve aceitar nenhum argumento e deve retornar sem argumentos. class_name deve ser um identificador válido do SQL Server e deve existir como uma classe na montagem com visibilidade de montagem. Se a classe tiver um nome qualificado no namespace que usa “.” para separar partes do namespace, o nome da classe deve ser delimitado usando delimitadores ou ” “. A classe não pode ser uma classe aninhada.

Nota

Por defeito, a capacidade do SQL Server para executar código CLR está desligada. É possível criar, modificar, e largar objectos de base de dados que referenciam módulos de código gerido, mas estas referências não são executadas numa instância do SQL Server, a menos que a opção clr esteja activada utilizando sp_configure.

Remarks for DML Triggers

DDML triggers são frequentemente utilizados para fazer cumprir as regras de negócio e a integridade dos dados. O SQL Server fornece integridade referencial declarativa (DRI) através das declarações ALTER TABLE e CREATE TABLE. No entanto, o DRI não fornece integridade referencial de bases de dados cruzadas. A integridade referencial refere-se às regras sobre as relações entre as chaves primárias e estrangeiras das tabelas. Para impor a integridade referencial, utilizar as restrições da CHAVE PRIMÁRIA e da CHAVE ESTRANGEIRA na TABELA ALTER e na TABELA CRIAR. Se existirem restrições na tabela de gatilho, elas são verificadas após o INSTEAD OF trigger runs e antes do AFTER trigger runs. Se as restrições forem violadas, o INSTEAD OF trigger actions é retrocedido e o AFTER trigger não é disparado.

É possível especificar o primeiro e o último AFTER triggers a serem executados numa tabela, usando sp_settriggerorder. Pode especificar apenas um primeiro e um último gatilho DEPOIS para cada operação INSERT, UPDATE, e DELETE sobre uma tabela. Se houver outros gatilhos DEPOIS na mesma tabela, eles são executados aleatoriamente.

Se uma instrução ALTER TRIGGER alterar um primeiro ou último gatilho, o primeiro ou último atributo definido no gatilho modificado é descartado, e é necessário repor o valor da ordem usando sp_settriggerorder.

Um gatilho DEPOIS é executado apenas depois do gatilho da instrução SQL ter sido executado com sucesso. Esta execução bem sucedida inclui todas as acções em cascata referenciais e verificações de restrição associadas ao objecto actualizado ou apagado. Um AFTER não dispara recursivamente um INSTEAD OF trigger na mesma tabela.

Se um INSTEAD OF trigger definido numa tabela executar uma instrução contra a tabela que normalmente dispararia novamente o INSTEAD OF trigger, o gatilho não é chamado recursivamente. Em vez disso, a declaração processa-se como se a tabela não tivesse INSTEAD OF trigger e inicia a cadeia de operações de restrição e APÓS as execuções do trigger. Por exemplo, se um gatilho for definido como um gatilho INSTEAD OF INSERT para uma tabela. E, o gatilho executa uma instrução INSERT na mesma tabela, a instrução INSERT lançada pelo INSTEAD OF trigger não volta a chamar o gatilho. O INSERT lançado pelo gatilho inicia o processo de execução de acções de restrição e disparo de qualquer AFTER INSERT gatilhos definidos para a tabela.

Quando um gatilho INSTEAD OF definido numa visualização executa uma declaração contra a visualização que normalmente dispararia o gatilho INSTEAD OF novamente, não é chamado recursivamente. Em vez disso, a declaração é resolvida como modificações contra as tabelas de base subjacentes à view. Neste caso, a definição da visualização deve cumprir todas as restrições para uma visualização actualizável. Para uma definição de vistas actualizáveis, ver Modificar Dados Através de uma Vista.

Por exemplo, se um gatilho for definido como um gatilho INSTEAD OF UPDATE para uma vista. E, o gatilho executa uma instrução UPDATE referenciando a mesma vista, a instrução UPDATE lançada pelo INSTEAD OF trigger não volta a chamar o gatilho. A UPDATE lançada pelo gatilho é processada contra a vista como se a vista não tivesse um INSTEAD OF trigger. As colunas alteradas pela UPDATE devem ser resolvidas para uma única tabela base. Cada modificação a uma tabela base subjacente inicia a cadeia de aplicação de restrições e disparo DEPOIS de disparos definidos para a tabela.

Testing for UPDATE or INSERT Actions to Specific Columns

You can design a Transact-SQL trigger to do fazer certas acções baseadas em UPDATE or INSERT modifications to specific columns. Utilize UPDATE() ou COLUMNS_UPDATED no corpo do gatilho para este fim. Testes de UPDATE() para tentativas de UPDATE ou INSERT em uma coluna. COLUMNS_UPDATED testes para acções de UPDATE ou INSERT que correm em múltiplas colunas. Esta função retorna um padrão de bits que indica que colunas foram inseridas ou actualizadas.

Trigger Limitations

CREATE TRIGGER deve ser a primeira declaração no lote e pode ser aplicada apenas a uma tabela.

Um gatilho é criado apenas na base de dados actual; contudo, um gatilho pode referenciar objectos fora da base de dados actual.

Se o nome do esquema de gatilho for especificado para qualificar o gatilho, qualifique o nome da tabela da mesma forma.

A mesma acção de trigger pode ser definida para mais de uma acção do utilizador (por exemplo, INSERT e UPDATE) na mesma declaração CREATE TRIGGER.

InSTEAD OF DELETE/UPDATE triggers não podem ser definidos numa tabela que tenha uma chave estrangeira com uma acção em cascata em DELETE/UPDATE definida.

A mesma declaração SET pode ser especificada dentro de um trigger. A opção SET seleccionada permanece em vigor durante a execução do disparo e depois volta à sua configuração anterior.

Quando um disparo de disparo é disparado, os resultados são devolvidos à aplicação de chamada, tal como nos procedimentos armazenados. Para evitar que os resultados sejam devolvidos a uma aplicação por causa de um disparo de gatilho, não incluir declarações SELECT que devolvem resultados ou declarações que realizam atribuições de variáveis num gatilho. Um disparo que inclua declarações SELECT que retornam resultados ao utilizador ou declarações que fazem atribuição de variáveis, requer um tratamento especial. Teria de escrever os resultados retornados em cada aplicação em que são permitidas modificações na tabela de disparo. Se a atribuição de variáveis tiver de ocorrer num gatilho, usar uma declaração SET NOCOUNT no início do gatilho para evitar o retorno de quaisquer conjuntos de resultados.

Embora uma declaração TRUNCATE TABLE seja efectivamente uma declaração DELETE, não activa um gatilho porque a operação não regista eliminações de linhas individuais. Contudo, apenas os utilizadores com permissões para executar uma declaração TRUNCATE TABLE precisam de se preocupar em contornar inadvertidamente um gatilho DELETE desta forma.

A declaração WRITETEXT, quer esteja registada ou não, não activa um gatilho.

As seguintes declarações Transact-SQL não são permitidas num gatilho DML:

  • BASE DE DADOS ÚLTIMOS
  • BASE DE DADOS DE CRÉDITO
  • BASE DE DADOS DE PROCURAÇÃO
  • BASE DE DADOS DE RESTAURO
  • OGRAMA DE RESTAURO
  • RECONFIGURA

Adicionalmente, as seguintes declarações Transact-SQL não são permitidas dentro do corpo de um gatilho DML quando este é utilizado contra a tabela ou visão que é o alvo da acção desencadeadora.

  • CREATE INDEX (incluindo CREATE SPATIAL INDEX e CREATE XML INDEX)
  • ALTER INDEX
  • DROP INDEX
  • DROP TABLE
  • DBCC DBREINDEX
  • ALTER FUNCTION PARTITION FUNCTION
  • ALTER TABLE quando usado para fazer o seguinte:
    • Adicionar, modificar, ou largar colunas.
    • Distribuir ou largar restrições PRIMÁRIAS ou ÚNICOS.

Nota

p>Porque o SQL Server não suporta triggers definidos pelo utilizador em tabelas do sistema, recomendamos que não crie triggers definidos pelo utilizador em tabelas do sistema.

Optimizar triggers DML

Triggers funcionam em transacções (implícitas ou não) e enquanto estão abertas, bloqueiam recursos. O bloqueio mantém-se até que a transacção seja confirmada (com COMMIT) ou rejeitada (com um ROLLBACK). Quanto mais longo for um gatilho, maior é a probabilidade de outro processo ser então bloqueado. Assim, a escrita dispara para diminuir a sua duração sempre que possível. Uma forma de conseguir uma duração mais curta é soltar um disparo quando uma instrução DML muda de linha zero.

Para soltar o disparo para um comando que não muda nenhuma linha, empregar a variável de sistema ROWCOUNT_BIG.

O seguinte trecho de código T-SQL mostra como soltar o disparo para um comando que não muda nenhuma linha. Este código deve estar presente no início de cada disparo DML:

IF (ROWCOUNT_BIG() = 0)RETURN;

Remarks for DDL Triggers

DDL triggers, como triggers padrão, lançam procedimentos armazenados em resposta a um evento. Mas, ao contrário dos gatilhos padrão, não funcionam em resposta a declarações UPDATE, INSERT, ou DELETE sobre uma tabela ou vista. Em vez disso, correm principalmente em resposta a declarações de linguagem de definição de dados (DDL). Os tipos de declarações incluem CREATE, ALTER, DROP, GRANT, DENY, REVOKE, e UPDATE STATISTICS. Certos procedimentos armazenados no sistema que executam operações semelhantes ao DDL também podem disparar gatilhos DDL.

Importante

Teste os gatilhos DDL para determinar as suas respostas à execução de procedimentos armazenados no sistema. Por exemplo, a declaração CREATE TYPE e os procedimentos armazenados tipo sp_add e sp_rename disparam um gatilho DDL que é criado num evento CREATE_TYPE.

Para mais informações sobre gatilhos DDL, ver gatilhos DDL.

DDL triggers não disparam em resposta a eventos que afectam tabelas temporárias locais ou globais e procedimentos armazenados.

Desaccionadores DML não semelhantes, os gatilhos DDL não são escopados para esquemas. Assim, não se pode usar funções como OBJECT_ID, OBJECT_NAME, OBJECTPROPERTY, e OBJECTPROPERTYEX para consultar metadados sobre gatilhos DDL. Utilize antes as vistas de catálogo. Para mais informações, ver Get Information About DDL Triggers.

Nota

Server-scoped DDL triggers aparecem na pasta Triggers do SQL Server Management Studio Object Explorer. Esta pasta está localizada sob a pasta Objectos do Servidor. Os gatilhos DDL com a escala de base de dados aparecem na pasta Database Triggers. Esta pasta está localizada sob a pasta Programmability da base de dados correspondente.

Acionadores de Logon

Acionadores de Logon realizam procedimentos armazenados em resposta a um evento LOGON. Este evento acontece quando uma sessão do utilizador é estabelecida com uma instância do SQL Server. O Logon dispara após a fase de autenticação do login terminar, mas antes de a sessão do utilizador ser estabelecida. Assim, todas as mensagens originadas dentro do disparo que normalmente chegariam ao utilizador, tais como mensagens de erro e mensagens da instrução PRINT, são desviadas para o registo de erros do SQL Server. Para mais informações, ver Gatilhos de Logon.

Acionadores de Logon não disparam se a autenticação falhar.

Transacções distribuídas não são suportadas num gatilho de logon. Erro 3969 volta quando um disparo de logon que contém um disparo de transacção distribuída.

Desactivar um disparo de logon

Um disparo de logon pode efectivamente impedir ligações bem sucedidas ao Motor de Base de Dados para todos os utilizadores, incluindo membros da função de servidor fixo sysadmin. Quando um gatilho de logon impede as ligações, os membros da função de servidor fixo sysadmin podem ligar-se usando a ligação dedicada de administrador, ou iniciando o Motor de Base de Dados no modo de configuração mínima (-f). Para mais informações, ver Database Engine Service Startup Options.

General Trigger Considerations

Returning Results

A capacidade de retornar resultados de triggers será removida numa versão futura do SQL Server. Os gatilhos que retornam conjuntos de resultados podem causar comportamentos inesperados em aplicações que não foram concebidas para funcionar com eles. Evitar a devolução de conjuntos de resultados de triggers em novos trabalhos de desenvolvimento, e planear modificar as aplicações que actualmente o fazem. Para evitar que os disparadores retornem conjuntos de resultados, defina a opção de não permitir o retorno dos resultados dos disparadores para 1.

Acionadores de Logon sempre não permitem o retorno dos conjuntos de resultados e este comportamento não é configurável. Se um disparo de logon gerar um conjunto de resultados, o disparo não é iniciado e a tentativa de login que disparou o disparo é negada.

Acionadores múltiplos

ServidorSQL permite criar múltiplos disparos para cada evento DML, DDL, ou LOGON. Por exemplo, se CREATE TRIGGER FOR UPDATE for executado para uma tabela que já tenha um gatilho UPDATE, é criado um gatilho de actualização adicional. Em versões anteriores do SQL Server, apenas um trigger para cada evento de modificação de dados INSERT, UPDATE, ou DELETE é permitido para cada tabela.

Acionadores Recursivos

SQL Server também suporta invocação recursiva de triggers quando a configuração RECURSIVE_TRIGGERS é activada usando ALTER DATABASE.

Accionadores recursivos permitem a ocorrência dos seguintes tipos de invocação:

  • Recurso indirecto

    Com a invocação indirecta, uma aplicação actualiza a tabela T1. Este disparo desencadeia TR1, actualizando a tabela T2. O gatilho T2 dispara e actualiza a tabela T1.

  • Reversão directa

    Na recursividade directa, a aplicação actualiza a tabela T1. Este disparo desencadeia TR1, actualizando a tabela T1. Porque a tabela T1 foi actualizada, o gatilho TR1 dispara novamente, e assim por diante.

O exemplo seguinte utiliza tanto a recursividade de gatilho indirecta como directa Assumir que dois gatilhos de actualização, TR1 e TR2, estão definidos na tabela T1. O gatilho TR1 actualiza a tabela T1 recursivamente. Uma declaração UPDATE executa cada TR1 e TR2 uma vez. Além disso, o lançamento de TR1 desencadeia a execução de TR1 (recursivamente) e TR2. As tabelas inseridas e apagadas para um disparo específico contêm linhas que correspondem apenas à instrução UPDATE que invocou o disparo.

Nota

O comportamento anterior só ocorre se a definição RECURSIVE_TRIGGERS for activada através da utilização de ALTER DATABASE. Não existe uma ordem definida na qual os gatilhos múltiplos definidos para um evento específico são executados. Cada disparo deve ser auto-contido.

Desactivar a definição RECURSIVE_TRIGGERS apenas previne as repetições directas. Para desactivar também a recursividade indirecta, definir a opção do servidor de triggers aninhados para 0 usando sp_configure.

Se qualquer um dos triggers executar uma TRANSACÇÃO ROLLBACK, independentemente do nível de aninhamento, não são executados mais triggers.

Nested Triggers

P>É possível aninhar triggers até um máximo de 32 níveis. Se um gatilho muda uma tabela na qual há outro gatilho, o segundo gatilho é activado e pode então chamar um terceiro gatilho, e assim por diante. Se qualquer gatilho na cadeia desencadeia um loop infinito, o nível de aninhamento é excedido e o gatilho é cancelado. Quando um gatilho Transact-SQL lança um código gerido por referência a uma rotina CLR, tipo, ou agregado, esta referência conta como um nível contra o limite de aninhamento de 32 níveis. Os métodos invocados de dentro do código gerido não contam contra este limite.

Para desactivar gatilhos aninhados, defina a opção de gatilhos aninhados de sp_configure para 0 (desligado). A configuração padrão suporta triggers aninhados. Se os gatilhos aninhados estiverem desligados, os gatilhos recursivos também são desactivados, apesar da configuração RECURSIVE_TRIGGERS que é definida usando ALTER DATABASE.

O primeiro gatilho DEPOIS aninhado dentro de um INSTEAD OF trigger dispara mesmo que a opção de configuração do servidor do gatilho aninhado seja 0. Mas, sob esta configuração, os gatilhos DEPOIS posteriores não disparam. Reveja as suas aplicações para triggers aninhados para determinar se as aplicações seguem as suas regras de negócio quando a opção de configuração do servidor de triggers aninhados é definida para 0. Caso contrário, faça as modificações apropriadas.

Deferred Name Resolution

SQL Server permite que os procedimentos armazenados Transact-SQL, triggers, e lotes se refiram a tabelas que não existem em tempo de compilação. Esta capacidade é chamada resolução diferida de nomes.

Permissões

Para criar um gatilho DML, requer a permissão de ALTER na tabela ou visualização em que o gatilho está a ser criado.

Para criar um gatilho DDL com escopo de servidor (ON ALL SERVER) ou um gatilho de logon, requer a permissão de CONTROL SERVER no servidor. Para criar um gatilho DDL com escopo de base de dados (EM BASE DE DADOS), requer ALTERAÇÃO DE QUALQUER TRIGGER DDL DE BASE DE DADOS na base de dados actual.

Exemplos

A. Usando um disparador DML com uma mensagem de lembrete

O seguinte disparador DML imprime uma mensagem para o cliente quando alguém tenta adicionar ou alterar dados na base de dados Customer tabela na base de dados AdventureWorks2012.

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

B. Usando um gatilho DML com uma mensagem de e-mail de lembrete

O exemplo seguinte envia uma mensagem de e-mail a uma pessoa especificada (MaryM) quando o Customer muda de tabela.

CREATE TRIGGER reminder2 ON Sales.Customer AFTER INSERT, UPDATE, DELETE AS EXEC msdb.dbo.sp_send_dbmail @profile_name = 'AdventureWorks2012 Administrator', @recipients = '[email protected]', @body = 'Don''t forget to print a report for the sales force.', @subject = 'Reminder'; GO 

C. Usando um gatilho DML DEPOIS para impor uma regra de negócio entre as tabelas PurchaseOrderHeader e Vendor

Porque as restrições CHECK referem-se apenas às colunas em que a restrição ao nível da coluna ou da tabela é definida, é necessário definir quaisquer restrições ao nível da tabela cruzada (neste caso, regras de negócio) como gatilhos.

O exemplo seguinte cria um gatilho DML na base de dados AdventureWorks2012. Este gatilho verifica se a classificação de crédito do fornecedor é boa (não 5) quando há uma tentativa de inserir um novo pedido de compra na tabela PurchaseOrderHeader. Para obter a classificação de crédito do fornecedor, a tabela Vendor deve ser referenciada. Se a classificação de crédito for demasiado baixa, aparece uma mensagem e a inserção não acontece.

-- 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 * FROM Purchasing.PurchaseOrderHeader AS p JOIN inserted AS i ON p.PurchaseOrderID = i.PurchaseOrderID JOIN Purchasing.Vendor AS v ON v.BusinessEntityID = p.VendorID WHERE v.CreditRating = 5 ) BEGIN RAISERROR ('A vendor''s credit rating is too low to accept new purchase orders.', 16, 1); ROLLBACK TRANSACTION; 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. Usando um gatilho DDL de base de dados

O exemplo seguinte usa um gatilho DDL para evitar que qualquer sinónimo numa base de dados seja descartado.

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. Usando um gatilho DDL com a opção server-scoped DDL

O exemplo seguinte usa um gatilho DDL para imprimir uma mensagem se ocorrer algum evento CREATE DATABASE na instância do servidor actual, e usa a função EVENTDATA para recuperar o texto da declaração Transact-SQL correspondente. Para mais exemplos que utilizam EVENTDATA em desencadeadores DDL, ver Usar a função EVENTDATA.

Applies to: SQL Server 2008 e posteriores.

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

F. Usando um disparador de logon

O seguinte exemplo de disparador de logon nega uma tentativa de iniciar sessão no SQL Server como membro do login_test login se já existirem três sessões de utilizador a correr sob esse login.

Aplica a: SQL Server 2008 e posteriores.

Aplica a: SQL Server 2008 e posteriores.

F: SQL Server 2008 e posteriores.

USE master; GO CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf' 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. Visualizando os eventos que provocam o disparo

O exemplo seguinte consulta o sys.triggers e sys.trigger_events visualizações de catálogo para determinar quais os eventos em linguagem Transact-SQL que provocam o disparo safety a disparar. O gatilho, safety, é criado no exemplo ‘D’, encontrado acima.

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

Ver Também

TABELA ALTERA (Transact-SQL)
TRIGGER ALTERA (Transact-SQL)
COLUMNS_UPDATED (Transact-SQL)
TABELA DE TRIBUTAÇÃO (Transact-SQL)>br>TRABELA DE TRIBUTAÇÃO (Transact-SQL)
DROP TRIGGER (Transact-SQL)
ENABLE TRIGGER (Transact-SQL)
DISABLE TRIGGER (Transact-SQL)
TRIGGER_NESTLEVEL (Transact-SQL)
EVENTDATA (Transact-SQL)
sys.dm_sql_referenced_entities (Transact-SQL)
sys.dm_sql_referencing_entities (Transact-SQL)
sys.sql_expression_dependencies (Transact-SQL)
sp_help (Transact-SQL)
sp_helptrigger (Transact-SQL)
sp_helptext (Transact-SQL)
sp_rename (Transact-SQL)>brSQL)
sp_settriggerorder (Transact-SQL)
UPDATE() (Transact-SQL)
Encontrar informação sobre gatilhos DML
Encontrar informação sobre gatilhos DDL
sys.gatilhos (Transact-SQL)
sys.trigger_events (Transact-SQL)
sys.sql_modules (Transact-SQL)
sys.assembly_modules (Transact-SQL)
sys.server_triggers (Transact-SQL)
sys.server_trigger_events (Transact-SQL)
sys.server_sql_modules (Transact-SQL)
sys.server_assembly_modules (Transact-SQL)

Leave a Comment

O seu endereço de email não será publicado. Campos obrigatórios marcados com *