Corrigé EFF 2013 Pratique Variante 2-3

2.
create proc PS1(@nom varchar(100) , @Prenom varchar(100))
as
begin
select * from Table_Journal where Compte = (Select compte from table_compte where nom = @nom and prenom =

@prenom)
end


3.
create proc PS2
as
begin
declare @compte int,
@nom varchar(50),
@prenom varchar(50),
@MotPasse varchar(100),
@DateCreation date,
@Tailleintiale varchar(50),
@TailleDisponible varchar(50),
@QuestionSecurite varchar(50),
@ReponseSecurite varchar(100),
@EtatCompte int
declare cur1 scroll cursor for select distinct c.* from table_compte c inner join Table_Courier tc on c.compte =

tc.CompteExpediteur inner join Table_FichierAttache tfa on tc.NCourier = tfa.NCourier where tfa.Format ='MS'
open cur1
Fetch next from cur1 into @compte ,@nom ,@prenom ,@MotPasse ,@DateCreation ,@Tailleintiale ,@TailleDisponible

,@QuestionSecurite ,@ReponseSecurite ,@EtatCompte
while(@@Fetch_status = 0)
begin
print 'NCompte : ' + cast(@compte as varchar)

Fetch next from cur1 into @compte ,@nom ,@prenom ,@MotPasse ,@DateCreation ,@Tailleintiale ,@TailleDisponible

,@QuestionSecurite ,@ReponseSecurite ,@EtatCompte
end
end
4.
create function TailleTotal(@Expediteur int) returns float
as
begin
declare @result float
set @result = (select Sum(TailleJournal ) from Table_Journal where Compte = @Expediteur)
return @result
end

6.
create trigger tr1 on Table_Journal
instead of insert , update
as
begin
declare @etatcourier varchar(50)
declare @Taillefichier float
declare @tailleDisponible float
set @etatcourier = (Select EtatCourier from Table_Courier where Ncourier = (Select Ncourier from inserted))
set @Taillefichier = (Select Taille from Table_FichierAttache where Ncourier =(Select Ncourier from

inserted))
set @tailleDisponible = (Select TailleDisponible*0.08 from table_compte where compte =(Select compte from

inserted))
if(@etatcourier = 'non' or @Taillefichier > @tailleDisponible)
print 'vous n avez pas le droit de l insere'
else
insert into Table_Journal select *