Exercice 4 : Contraintes Triggers Fonctions + Correction

Voici la partie 4 des exercices SQL SERVER Corrigés .

Je remercie le membre COUL qui nous à proposé cet exercice .

C’est l’un des exercices importants pour améliorer vos connaissances au Langage SQL SERVER 2008.

create database prepa1

go

use prepa1

go

create table Membres

(

num_mbr int primary key,

nom_mbr varchar(50),

date_adhesion date,

adresse_mbr varchar(100)

)

create table Congres

(

code_cgn varchar(8) primary key,

theme_cgn varchar(100),

pays varchar(30),

duree_cgn int,

nb_participant_cgn int,

montant_fixe float,

montant_total_cotise float

)

create table Participation

(

num_mbr int foreign key references Membres(num_mbr) on delete cascade,

code_cgn varchar(8) foreign key references Congres(code_cgn)  on delete cascade,

primary key(num_mbr,code_cgn),

montant_cotise float

)

/*2*/

/*a*/

alter table Congres

add constraint default_mt

default 0 for montant_total_cotise

/*b*/

alter table Membres

alter column nom_mbr varchar(50) not null

/*c*/

alter table Congres

add constraint default_nbr_part

default 0 for nb_participant_cgn

/*d*/

/*e*/

alter table Congres

add constraint unique_theme

unique (Theme_cgn)

/*f*/

alter table Congres

add constraint check_montant

check(montant_fixe<montant_total_cotise)

/*g*/

alter table Congres

add Constraint check_mt_pos

check(montant_total_cotise>0)

/*3*/

create trigger T3

on Participation

for insert

as

declare @code_cgn varchar(8)

declare @montant_cotise float

select @code_cgn=code_cgn from inserted

select @montant_cotise=montant_cotise from inserted

update Congres set montant_total_cotise+=@montant_cotise,nb_participant_cgn+=1

/*4*/

alter function f4(@numero_mbr int)

returns varchar(20)

as

begin

declare @montant float

declare @code_cgn varchar(8)

declare @chaine_retour varchar(20)

declare @montant_moyen float

select @montant=montant_cotise from Participation where @numero_mbr=num_mbr

select @code_cgn=code_cgn from participation where @numero_mbr=num_mbr

select @montant_moyen=avg(montant_total_cotise)/SUM(nb_participant_cgn) from Congres

where code_cgn=@code_cgn

if(@montant_moyen>@montant)

set @chaine_retour='NON VIP'

else

set @chaine_retour='VIP'

return @chaine_retour

end

insert into Membres values(0,'M1',getDate(),'Adresse 1')

insert into Membres values(1,'M2',getDate(),'Adresse 2')

insert into Congres values('CODE1','Theme 1','P1',0,0,0,1)

insert into Participation values(0,'CODE1',20)

insert into Participation values(1,'CODE1',2)