Gatilho SQLite

Resumo : este tutorial discute o gatilho SQLite, que é um objeto de banco de dados disparado automaticamente quando os dados em uma tabela são alterados.

O que é um gatilho SQLite

Um gatilho SQLite é um objeto de banco de dados nomeado que é executado automaticamente quando uma INSERTinstrução UPDATE ou DELETE é emitida na tabela associada.

Quando precisamos de gatilhos SQLite

Você costuma usar gatilhos para permitir auditorias sofisticadas. Por exemplo, você deseja registrar as alterações nos dados confidenciais, como salário e endereço, sempre que eles forem alterados.

Além disso, você usa gatilhos para impor regras de negócios complexas centralmente no nível do banco de dados e evitar transações inválidas.

CREATE TRIGGERInstrução SQLite

Para criar um novo gatilho no SQLite, você usa a CREATE TRIGGERseguinte instrução:

CREATE TRIGGER [IF NOT EXISTS] trigger_name 
   [BEFORE|AFTER|INSTEAD OF] [INSERT|UPDATE|DELETE] 
   ON table_name
   [WHEN condition]
BEGIN
 statements;
END;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Nesta sintaxe:

• Primeiro, especifique o nome do gatilho após as CREATE TRIGGERpalavras-chave.
• Em seguida, determine quando o gatilho é acionado, como BEFORE, AFTERou INSTEAD OF. Você pode criar BEFOREgatilhos AFTERem uma tabela. No entanto, você só pode criar um INSTEAD OFgatilho em uma visualização.
• Em seguida, especifique o evento que faz com que o gatilho seja invocado, como INSERT, UPDATEou DELETE.
• Depois disso, indique a tabela à qual pertence o gatilho.
• Por fim, coloque a lógica do gatilho no BEGIN ENDbloco, que pode ser qualquer instrução SQL válida.

Se você combinar o momento em que o gatilho é acionado e o evento que faz com que o gatilho seja acionado, você terá um total de 9 possibilidades:

• BEFORE INSERT
• AFTER INSERT
• BEFORE UPDATE
• AFTER UPDATE
• BEFORE DELETE
• AFTER DELETE
• INSTEAD OF INSERT
• INSTEAD OF DELETE
• INSTEAD OF UPDATE

Suponha que você use uma UPDATEinstrução para atualizar 10 linhas em uma tabela, o gatilho associado à tabela é acionado 10 vezes. Este gatilho é chamado FOR EACH ROWde gatilho. Se o gatilho associado à tabela for disparado uma vez, chamamos esse gatilho de FOR EACH STATEMENTgatilho.

A partir da versão 3.9.2, o SQLite suporta apenas FOR EACH ROWgatilhos. Ainda não apoiou os FOR EACH STATEMENTgatilhos.

Se você usar uma condição na WHENcláusula, o gatilho só será invocado quando a condição for verdadeira. Caso você omita a WHENcláusula, o gatilho é executado para todas as linhas.

Observe que se você eliminar uma tabela , todos os gatilhos associados também serão excluídos. No entanto, se o gatilho fizer referência a outras tabelas, o gatilho não será removido ou alterado se outras tabelas forem removidas ou atualizadas.

Por exemplo, um gatilho faz referência a uma tabela chamada people, você descarta a peopletabela ou a renomeia, é necessário alterar manualmente a definição do gatilho.

Você pode acessar os dados da linha que está sendo inserida, excluída ou atualizada usando as referências OLDe NEWno formato: OLD.column_namee NEW.column_name.

as referências OLDe NEWestão disponíveis dependendo do evento que faz com que o gatilho seja disparado.

A tabela a seguir ilustra as regras.:

Ação	Referência
INSERIR	NOVO está disponível
ATUALIZAR	NOVOS e ANTIGOS estão disponíveis
EXCLUIR	ANTIGO está disponível

Exemplos de gatilhos SQLite

Vamos criar uma nova tabela chamada leads para armazenar todos os leads de negócios da empresa.

CREATE TABLE leads (
	id integer PRIMARY KEY,
	first_name text NOT NULL,
	last_name text NOT NULL,
	phone text NOT NULL,
	email text NOT NULL,
	source text NOT NULL
);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

BEFORE INSERT1) Exemplo de gatilho SQLite

Suponha que você queira validar o endereço de e-mail antes de inserir um novo lead na leadstabela. Nesse caso, você pode usar um BEFORE INSERTgatilho.

Primeiro, crie um BEFORE INSERTgatilho da seguinte maneira:

CREATE TRIGGER validate_email_before_insert_leads 
   BEFORE INSERT ON leads
BEGIN
   SELECT
      CASE
	WHEN NEW.email NOT LIKE '%_@__%.__%' THEN
   	  RAISE (ABORT,'Invalid email address')
       END;
END;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Utilizamos a NEWreferência para acessar a coluna email da linha que está sendo inserida.

Para validar o email, utilizamos o LIKE operador para determinar se o email é válido ou não com base no padrão de email. Se o email não for válido, a RAISEfunção aborta a inserção e emite uma mensagem de erro.

Segundo, insira uma linha com um email inválido na leadstabela.

INSERT INTO leads (first_name,last_name,email,phone)
VALUES('John','Doe','jjj','4089009334');Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

SQLite emitiu um erro: “Endereço de email inválido” e abortou a execução da inserção.

Terceiro, insira uma linha com um email válido.

INSERT INTO leads (first_name, last_name, email, phone)
VALUES ('John', 'Doe', '[email protected]', '4089009334');Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Como o e-mail é válido, a instrução insert foi executada com sucesso.

SELECT
	first_name,
	last_name,
	email,
	phone
FROM
	leads;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

AFTER UPDATE2) Exemplo de gatilho SQLite

Os telefones e e-mails dos leads são tão importantes que você não pode perder essas informações. Por exemplo, alguém acidentalmente atualiza o e-mail ou telefone para os errados ou até mesmo os exclui.

Para proteger esses dados valiosos, você usa um gatilho para registrar todas as alterações feitas no telefone e no e-mail.

Primeiro, crie uma nova tabela chamada lead_logspara armazenar os dados históricos.

CREATE TABLE lead_logs (
	id INTEGER PRIMARY KEY,
	old_id int,
	new_id int,
	old_phone text,
	new_phone text,
	old_email text,
	new_email text,
	user_action text,
	created_at text
);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Segundo, crie um AFTER UPDATEgatilho para registrar dados na lead_logstabela sempre que houver uma atualização na coluna emailou phone.

CREATE TRIGGER log_contact_after_update 
   AFTER UPDATE ON leads
   WHEN old.phone <> new.phone
        OR old.email <> new.email
BEGIN
	INSERT INTO lead_logs (
		old_id,
		new_id,
		old_phone,
		new_phone,
		old_email,
		new_email,
		user_action,
		created_at
	)
VALUES
	(
		old.id,
		new.id,
		old.phone,
		new.phone,
		old.email,
		new.email,
		'UPDATE',
		DATETIME('NOW')
	) ;
END;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Você percebe que a condição da WHENcláusula especifica que o gatilho é invocado somente quando há uma alteração na coluna email ou telefone.

Terceiro, atualize o sobrenome de Johnfrom Doepara Smith.

UPDATE leads
SET 
   last_name = 'Smith'
WHERE
   id = 1;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

O gatilho log_contact_after_update não foi invocado porque não houve alteração no e-mail ou telefone.

Quarto, atualize o e-mail e o telefone Johnpara os novos.

UPDATE leads
SET 
   phone = '4089998888',
   email = '[email protected]'
WHERE
   id = 1;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Se você verificar a tabela de log, verá que há uma nova entrada lá.

SELECT
   old_phone, 
   new_phone, 
   old_email, 
   new_email, 
   user_action
FROM
   lead_logs;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Você pode desenvolver os gatilhos AFTER INSERTe AFTER DELETEpara registrar os dados na lead_logstabela como um exercício.

DROP TRIGGERInstrução SQLite

Para eliminar um gatilho existente, você usa a DROP TRIGGERinstrução a seguir:

DROP TRIGGER [IF EXISTS] trigger_name;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Nesta sintaxe:

• Primeiro, especifique o nome do gatilho que deseja eliminar após as DROP TRIGGERpalavras-chave.
• Segundo, use a IF EXISTSopção de excluir o gatilho somente se ele existir.

Observe que se você eliminar uma tabela, o SQLite eliminará automaticamente todos os gatilhos associados à tabela.

Por exemplo, para remover o validate_email_before_insert_leadsgatilho, você usa a seguinte instrução:

DROP TRIGGER validate_email_before_insert_leads;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Neste tutorial, apresentamos os gatilhos SQLite e mostramos como criar e eliminar gatilhos do banco de dados.