Triggers  - Soluções


1) Create a trigger that prevents the insertion of a rental whose date is less than the birthdate
of the member (sócio) who makes that rental.

sqlite> CREATE TRIGGER TrigImpedeAluguerAntesDoSocioNascer BEFORE INSERT ON aluguer
 BEGIN  
SELECT CASE  
 WHEN (select 1 from socio where num_socio = NEW.num_socio and 
 julianday(data_nsc_socio) >= julianday(NEW.data_aluguer))
THEN
 RAISE(ABORT,'Nao pode alugar videos antes de nascer')
 END;
END;


2) 	create a trigger that prevents the insertion of a rental whose movie copy is rented.

Two steps solution: first you create a view that indicates the length of time each
copy has been or is still rented.

sqlite> Create view copiasalugadas as select cod_filme,num_copia,data_aluguer,
date('now') as data_fim from aluguer where cod_filme||' '||num_copia||' '||data_aluguer not in
 (select cod_filme||' '||num_copia||' '||data_aluguer from devolucao)
 union
 select cod_filme,num_copia,data_aluguer,data_devolucao from aluguer natural inner join
  devolucao;
  
 %Now you create the trigger:
 
 sqlite> CREATE TRIGGER TrigImpedeAluguerDeCopiaAlugada BEFORE INSERT ON aluguer
 BEGIN  
SELECT CASE  
 WHEN (select 1 from copiasalugadas where cod_filme = NEW.cod_filme and
  num_copia = NEW.num_copia and julianday(NEW.data_aluguer) between
 julianday(data_aluguer) and julianday(data_fim))
THEN
 RAISE(ABORT,'Essa cópia está emprestada. Não pode ser alugada')
 END;
END;