Triggers em SQLite Triggers

Propaganda

Triggers em SQLite
Fernando Lobo
Base de Dados, Universidade do Algarve
1 / 15
Triggers
Um trigger permite que uma determinada sequência de comandos
SQL seja accionada quando um determinado evento ocorre.
O evento pode ser INSERT, UPDATE, ou DELETE.
O trigger pode ser accionado imediatamente antes (BEFORE) ou
imediatamente depois (AFTER) de cada evento.
2 / 15
Triggers
Quase todos os SGBD permitem a especificação de triggers.
Existem ligeiras diferenças de SGBD para SGBD.
Os exemplos que se seguem foram testados com SQLite. Poderão
não ter uma sintaxe ligeiramente diferente noutros SGBD.
3 / 15
Exemplo
Ao inserir um filme pretende-se que a sua duração seja truncada para um
valor máximo de 300 minutos no caso de se tentar inserir um valor
superior.
Associamos um trigger à tabela de filmes.
O trigger será accionado logo após um insert à tabela de filmes.
CREATE TRIGGER trunca_duracao AFTER INSERT ON Filmes
WHEN
NEW.duracao > 300
BEGIN
UPDATE Filmes
SET duracao=300
WHERE nome=NEW.nome AND ano=NEW.ano;
END;
4 / 15
Exemplo (cont.)
Podemos aceder aos tuplos que estão a ser inseridos, alterados, ou
apagados, através das variáveis NEW e OLD.
I
NEW refere-se aos novos tuplos (no caso de inserts e updates)
I
OLD refere-se aos velhos tuplos (no caso de deletes e updates)
No exemplo anterior, NEW.duracao refere-se à duração do filme que
acaba de ser inserido.
Caso essa duração seja superior a 300, o código SQL que está entre
BEGIN e END é executado.
5 / 15
Exemplo (cont.)
Durante a inserção de um filme, a duração é truncada para 300
minutos. E no caso de um update? O que acontece?
Teremos de fazer um trigger quando o evento for um update.
CREATE TRIGGER trunca_duracao_updt AFTER UPDATE ON Filmes
WHEN
NEW.duracao > 300
BEGIN
UPDATE Filmes
SET duracao=300
WHERE nome=NEW.nome AND ano=NEW.ano;
END;
6 / 15
Outro exemplo
Ao inserir um filme na tabela Filmes, se o estúdio não existir, então
automaticamente inserir esse estúdio na tabela Estúdios (com
morada a NULL).
CREATE TRIGGER insere_estudio BEFORE INSERT ON Filmes
WHEN
NOT EXISTS (SELECT * FROM Estudios
WHERE nome=NEW.nomeEstudio)
BEGIN
INSERT INTO Estudios(nome) VALUES (NEW.nomeEstudio);
END;
7 / 15
Trigger idêntico para para tratar da inserção do realizador caso não
exista.
CREATE TRIGGER insere_realizador BEFORE INSERT ON Filmes
WHEN
NOT EXISTS (SELECT * FROM Realizadores
WHERE nome=NEW.nomeRealizador)
BEGIN
INSERT INTO Realizadores(nome)
VALUES (NEW.nomeRealizador);
END;
8 / 15
Triggers e Views
As views são “read-only”, mas podemos simular a “escrita” em views
utilizando triggers.
Exemplo: criar um trigger de modo a podermos “inserir” tuplos na
view filmes disney.
CREATE VIEW filmes_disney AS
SELECT nome, ano, nomeRealizador
FROM Filmes
WHERE nomeEstudio = ’Disney’;
9 / 15
Exemplo (cont.)
CREATE TRIGGER ins_filmeDisney INSTEAD OF INSERT ON filmes_disney
BEGIN
INSERT INTO Filmes (nome, ano, nomeRealizador, nomeEstudio)
VALUES (NEW.nome, NEW.ano, NEW.nomeRealizador, ’Disney’);
END;
Agora podemos executar o seguinte comando,
INSERT INTO filmes_disney
VALUES (’The Huntchback of Notre Dame’,
1995, ’Steven Spielberg’);
O filme The Huntchback of Notre Dame de 1995 é inserido na tabela
Filmes, com nomeRealizador=’Steven Spielberg’ e nomeEstudio=’Disney’.
10 / 15
Outro exemplo
Vamos supor que temos uma view filmes actores que faz um join
de Filmes com Actores.
Podemos simular a inserção de dados na view filmes actores
CREATE VIEW filmes_actores AS
SELECT F.nome AS filme, F.ano AS ano,
F.nomeEstudio AS estudio,
F.nomeRealizador AS realizador,
P.nomeActor AS actor
FROM Filmes AS F, Participa AS P
WHERE F.nome = P.nomeFilme
AND F.ano = P.anoFilme;
A inserção de um tuplo na view, pode fazer com que tenhamos de
inserir tuplos na tabela Filmes, Estudios, Realizadores, Actores
e Participa.
11 / 15
Outro exemplo (cont.)
CREATE TRIGGER insere_em_filmes_actores
INSTEAD OF INSERT ON filmes_actores
BEGIN
INSERT INTO Filmes(nome, ano, nomeEstudio, nomeRealizador)
VALUES (NEW.filme, NEW.ano, NEW.estudio, NEW.realizador);
INSERT INTO Actores(nome) VALUES (NEW.actor);
INSERT INTO Estudios(nome) VALUES (NEW.estudio);
INSERT INTO Realizadores(nome) VALUES (NEW.realizador);
INSERT INTO Participa(nomeFilme,anoFilme,nomeActor)
VALUES (NEW.filme, NEW.ano, NEW.actor);
END;
12 / 15
Outro exemplo (cont.)
Agora podemos inserir dados na view filmes actores.
INSERT INTO filmes_actores
VALUES (’Taxi Driver’, 1978, ’Universal’,
’Martin Scorcese’, ’Roxana Arquette’);
13 / 15
Os inserts podem dar erro porque podem violar restrições que hajam
nas tabelas. Por exemplo, se o estúdio Universal já existir na tabela
de estúdios, o insert falha porque o nome do estúdio é chave
primária.
A solução para este problema é criar triggers adicionais. Por
exemplo:
CREATE TRIGGER nao_insere_estudios_duplicados
BEFORE INSERT ON Estudios
WHEN
EXISTS (SELECT * FROM Estudios WHERE nome=NEW.nome)
BEGIN
SELECT RAISE(IGNORE);
END;
14 / 15
A inserção de dados na view “esconde” a inserção efectiva dos
dados nas tabela base.
Ao inserir dados na view ficamos impossibilitados de inserir todos os
atributos das tabelas base.
Esses atributos ficam com o valor NULL (ou com o valor que estiver
especificado como DEFAULT).
15 / 15