Lista 09 triggers com notas

Prévia do material em texto

Considere o esquema relacional abaixo: 
 
Amigo (aid: integer, anome: string, sexo: char, idade: integer) 
Presente (pid: integer, pdescricao: string, pvalor: real) 
ListaPresente (aid: integer, pid: integer, preferência: integer) 
AmigoSecreto(aid: integer, aid_amigo: integer, pid_recebido: integer) 
 
 Os campos sublinhados constituem a chave primária das relações. 
 O campo “preferência” na relação ListaPresente representa a ordem de 
preferência do presente na lista de presentes de um determinado participante 
da brincadeira (amigo). Assim, uma tupla com valores <3, 2, 1> na relação 
ListaPresente indica que o presente de pid=2, que está na lista de preferências 
do amigo de aid=3, é o presente mais desejado por ele (preferência=1). Logo, 
se preferência fosse igual a 2, esse seria o segundo presente mais desejado 
pelo amigo, e assim sucessivamente. 
 Uma tupla com valores <10, 5, 2> na relação AmigoSecreto indica que o amigo 
de aid=10 tirou (ou tem como amigo secreto) o amigo de aid_amigo=5 e deu o 
presente de pid_recebido =2 (esse presente pode ou não estar na lista de 
preferências do amigo que ganhou o presente (no caso, o aid_amigo=5)). 
 
1. Questão 
Defina um gatilho (“trigger”) a fim de impedir que menores de 18 participem da 
brincadeira de amigo secreto. 
 
2. Questão 
Defina um gatilho (“trigger”) a fim de impedir que a lista de presentes de um convidado 
tenha mais de dez itens e que o valor total dos presentes (soma dos valores dos presentes) 
na lista seja maior que R$ 500,00. 
3. Questão 
Escreva um procedimento armazenado que recebe como parâmetro de entrada o 
identificador de um amigo e escolhe (retorna) o presente mais barato que consta na lista de 
presentes do seu amigo secreto. 
4. Questão 
Escreva um procedimento armazenado que recebe como parâmetro de entrada o 
identificador de um presente e insere este presente na lista de preferência de todos os 
participantes (amigos), caso o presente ainda não esteja na lista do participante (amigo). 
9º Lista de Fundamento de Banco de Dados 
Campus UFC – Quixadá 
Professora: Ticiana Linhares 
Engenharia de Software 
2013.2 
wellington
Note
CREATE OR REPLACE FUNCTION questao1_ins () RETURNS trigger ASnullnull$BODY$nullnull BEGINnullnull if new.idade>= 18 THENnullnull RETURN new;nullnull else RETURN null;nullnull End if;nullnull END;nullnull$BODY$nullnullLANGUAGE 'plpgsql' ;nullnullnullnullCREATE TRIGGER "ins_amigo"nullnullBEFORE INSERTnullnullON "amigo"nullnullFOR EACH ROW -- para qualquer inserção ou para cada linhanullnullEXECUTE PROCEDURE questao1_ins();nullnullnullnullCREATE TRIGGER "ins_amigo_update"nullnullBEFORE UPDATEnullnullON "amigo"nullnullFOR EACH ROWnullnullEXECUTE PROCEDURE questao1_ins();nullnull
Andre
Note
2) Ao inserir em lista presentenullnull Ao update em presentenullnullnullnullcreate function questao2-insert()nullnulldeclarenullnull qtdPresente int;nullnull somaPresente real;nullnull precoPresente real;nullnullbeginnullnull select count(*) into qtdPresentenullnull from listapresentenullnull where aid=new.aidnullnullnullnull select sum(p.valor) into somaPresentenullnull from listapresente (p. preente p where lp.aid=new.aid and p.pid=lp.pidnullnullnullnull select p.valor into precoPresentenullnull from presente pnullnull where p.pid=new.pid nullnullnullnull if(qtdPresente<=10 and somaPresente+precoPresente<500)nullnull then return new;nullnull else return null;nullnullEnd; nullnullnullnullnullnullnullnullcreate trigger questao2_insertnullnullbefore insert on listaPresentenullnullfor each rownullnullexecute procedure questao2_insertnullnull-----------------------------------------------------nullnullcreate function questao2_update return trigger ASnullnulldeclarenullnull cursor1 cursor is select aid from amigo A, listaPresente lp where A.aid = lp.aid and lp.pid=new.pidnullnull idAmigo int;nullnull somaPResente real;nullnullbeginnullnull open cursor1nullnull fetch cursor1 into idAmigo;nullnull loopnullnull exit when not found;nullnull select sum(p.valor) into somaPresentenullnull from listaPresente lp, presente p nullnull where lp.aid = idAmigonullnull and lp.pid = p.pidnullnull if(somaPResente+new.pvaor-old.pvalor>500)nullnull then return null;nullnull fetch cursor1 into idAmigo;nullnull nullnull end if;nullnull end loop;nullnull close cursor1;nullnull return new;nullnullend;nullnullnullnullnullnullcreate trigger questao2_update triggernullnullbefore update on presentenullnullfor each rownullnullexecute procedure questao2_update
Andre
Note
create function questao3(idamigo int)nullnullreturn int ASnullnulldeclarenullnull idPResente int;nullnullbeginnullnull select mim(p.pvalor), lp.pid into idPresente, nullnull from AmigoSecreto ANS, Presente p,nullnull ListaPresente lpnullnull where ANS.aid=idamigo and ANS.aid.amigo =lp.ai and lp.pid=p.pidnullnullreturn idPresente;nullnullend;nullnullnullnullnullnullnullnullnullnullnullnull
Andre
Note
create function questao4(idPresente int)nullnullreturn void ASnullnull$body$nullnulldeclarenullnull cursor1 cursor is select aid from amigo;nullnull idAmigo int;nullnull preferencia int;nullnullbeginnullnull open cursor1;nullnull fetch cursor1 into idAmigo;nullnull loopnullnull exit when not foundnullnullnullnull--> verificar se idAmigo já tem o presente idPresentenullnull if([select count(*) from listaPresente lp where lp.aid=idAmigo and lp.pid=idPresente]=0)nullnull select max(preferencia) into preferncianullnull from listaPresente lpnullnull where lp.aid=idAmigo;nullnull insert nto listaPresentenullnull values(idAmigo, idPresente, preferenica+1);nullnull end if;nullnull fetch cursor1 into idAmigo;nullnull end loop;nullnullclose cursor1;nullnullend;nullnull$body$nullnull nullnull
Para cada inserção a ser realizada utilize como valor para o atributo “preferência” o maior 
valor existente para este atributo na lista de presentes do participante (amigo) adicionado 
de uma unidade.