lab07e08

Departamento de Engenharia Informática
Sistemas de Informação e Bases de Dados
Triggers e Stored Procedures
O ficheiro bank.sql contém um conjunto de instruções SQL para criar a base de dados de
exemplo usada neste lab.
Exercícios para resolver
Teste devidamente todas as funções, procedimentos e triggers que desenvolver, usando
apenas a linha de comando e comandos SQL.
1. Implemente as seguintes funcionalidades:
(a) Escreva uma função em SQL que devolve o saldo absoluto de um cliente, isto é, a
diferença entre (1) todo o dinheiro que esse cliente tem em contas e (2) todas as
quantias que deve em empréstimos ao banco. A função deve ter um parâmetro
que identifica o cliente cujo saldo absoluto se pretende obter.
(b) Escreva um procedimento em SQL que concede automaticamente novos
empréstimos a clientes que obedeçam a uma certa condição. Essa condição é que
o saldo total em contas desse cliente seja superior a 1000€. O novo empréstimo
deve ser criado com os seguintes dados:
 A quantia do empréstimo é igual a metade do saldo da maior conta do
cliente.
 A agência do empréstimo é a mesma agência dessa conta.
 O número do empréstimo é igual ao número dessa conta.
(c) O procedimento anterior só é executado quando alguém o invocar, mas a intenção
é que seja executado automaticamente sempre que o saldo total em contas de um
cliente ultrapassar 1000€. Escreva um trigger que dispare esse procedimento
automaticamente mediante essas condições.
2. Escreva os comandos SQL necessários para responder a cada uma das seguintes
questões:
(a) Escreva uma função que devolve a diferença entre o saldo médio das contas em
duas agências dadas. A função tem dois parâmetros que identificam as agências a
comparar.
(b) Usando a função desenvolvida na alínea anterior, escreva uma consulta que
permita determinar qual é a agência cujo saldo médio das contas é o maior entre
todas as agências.
(c) A tabela depositor associa clientes a contas. Escreva um comando em SQL para
alterar a tabela depositor, de modo a que se uma conta for apagada da tabela
account, os registos na tabela depositor que referem essa conta também são
apagados.
IST/DEI
Pág. 1 de 2
BD
(d) Repita a alínea anterior para o caso análogo que se aplica aos empréstimos e que
envolve as tabelas borrower e loan.
(e) Escreva um procedimento que apague todos os empréstimos concedidos
automaticamente pelo procedimento da alínea 1(b). Suponha que entre a criação
dos empréstimos e a sua remoção não houve alteração aos saldos das contas.
3. Implemente os seguintes triggers:
(a) Escreva um trigger que elimina um empréstimo quando a respectiva quantia
(amount) em dívida chegar a zero. O trigger deve reflectir as modificações também
na tabela borrower.
(b) Por cada novo titular adicionado a uma conta, o banco oferece um bónus de 10€
nessa conta. Escreva um trigger que implemente este bónus de forma automática.
(c) O banco pretende recuperar o bónus concedido quando um cliente deixa de estar
associado a uma conta. Escreva um trigger para retirar o bónus da conta de forma
automática.
(d) O banco vai dar benefícios a clientes que decidam mudar-se para uma cidade onde
já exista uma agência. Se o cliente fizer isso, o banco aplica uma redução de 15€ a
todos os empréstimos desse cliente. Escreva um trigger para aplicar esta redução
de forma automática.
(e) O banco decidiu que vai deixar de conceder empréstimos a clientes que não
estejam associados a contas. Escreva um trigger para, nesse caso, impedir a
associação do cliente ao empréstimo. O trigger deve produzir uma mensagem de
erro apropriada.
IST/DEI
Pág. 2 de 2