Binome :
LAKHDOURA Youness
LAZAR Adnane
-creation de la base de donne :
create database TP6
-creation des tables :
use TP6
create table editeur(
numedi int primary key,
nomedi varchar (50),
adredi varchar (50)
)
create table adherent(
numadh int primary key,
nomadh varchar (50),
prenomadh varchar (50),
datenaissance date
)
create table livre(
numlive int primary key ,
titrelive varchar(50) ,
nbrexemlairedisponible int ,
numedi int foreign key references editeur(numedi)
)
create table emprunt(
numemp int primary key,
datemp date,
numadh int foreign key references adherent(numadh) ,
numliv int foreign key references livre(numiv)
)
create table retour(
numret int primary key ,
dateret date,
numemp int foreign key references emprunt(numemp)
)
-insertion des valeurs :
insert into editeur values
(1,'editeur1','benimellal'),(2,'editeur2','marrakech'),(3,'editeur3'
,'rabat'),(4,'eediteur4','casa'),(5,'editeur5','casa')
insert into livre values
(2,'livre2',30,1),(3,'livre3',15,5),(4,'livre4',30,1),(5,'livre5',20
,3)
insert into adherent
values
(2,'lazar','adnane','1997-04-05'),
(3,'kadiri','mohamed','2001-03-05'),
(4,'ghanim','amine','2000-12-01')
insert into emprunt
values
(1,'2017-12-22','2','3'),
(2,'2017-08-10','1','1'),
(3,'2017-05-14','2','3'),
(4,'2017-01-20','4','2')
insert into retour values
(1,'2017-12-28','2'),
(2,'2017-08-20','1'),
(3,'2017-05-24','2'),
(4,'2017-01-30','4')
6-
a/
Create procedure q6a
as
declare @a int ,@b varchar(50) ,@c varchar (50)
declare c1 cursor
for
select l.numlive,l.titrelive, e.nomedi
from livre l , editeur e
where l.numedi=e.numedi
open c1
fetch next from c1 into @a,@b,@c
while @@FETCH_STATUS=0
begin
print'le numero du livre :'+convert(varchar(20),@a)+',son titre est
:'+@b+'.Il été édité par :'+@c
fetch next from c1 into @a,@b,@c
end
close c1
deallocate c1
exec q6a
b/
create procedure q6b
as
declare @a int ,@b varchar(50) ,@c varchar (50)
declare c2 cursor
for
select l.numlive,l.titrelive, e.nomedi
from livre l , editeur e
where l.numedi=e.numedi and l.numlive not in(
select emprunt.numliv
from emprunt
)
open c2
fetch next from c2 into @a,@b,@c
while @@FETCH_STATUS=0
begin
print'le numero du livre :'+convert(varchar(20),@a)+',son titre est
:'+@b+'.Il été édité par :'+@c
fetch next from c2 into @a,@b,@c
end
close c2
deallocate c2
exec q6b
c/
Create procedure q6c
as
select l.numlive,l.titrelive,a.numadh,a.nomadh, e.datemp
from livre l , adherent a , emprunt e
where l.numlive=e.numliv and e.numadh=a.numadh and e.numemp not in(
select r.numemp
from retour r
)
exec q6c
d/
create procedure q6d
as
select l.numlive,l.titrelive
from livre l
where l.nbrexemlairedisponible!=0
exec q6d
e/
procedure
create procedure q6e @liv varchar(50), @a int output
as
if (select l.numlive
from livre l
where l.titrelive=@liv)
in (select e.numliv
from emprunt e
)
SET @a=1
else SET @a=0
declare @b int
exec q6e 'livre2',@b output
print @b
fonction
create Function q6efun(@liv varchar(50)) returns int
as
begin
declare @a int
if (select l.numlive
from livre l
where l.titrelive=@liv)
in (select e.numliv
from emprunt e
)
set @a=1
else set @a='0'
return @a
end
declare @i int
set @i=dbo.q6efun('livre2')
print @i
f/
procedure :
create procedure q6f @c varchar(50) output as
declare @a int ,@b int
declare @tab table (nombrelivre int , numedit int)
insert into @tab
select count(numlive) , numedi
from livre
group by numedi
declare c cursor for select * from @tab
order by nombrelivre desc
open c
fetch next from c into @a,@b
set @c= (select e.nomedi
from editeur e
where e.numedi=@b)
close c
deallocate c
declare @x varchar(50)
exec q6f @x output
print @x
function :
create function q6ffun()
returns varchar(50)
as
begin
declare @a int ,@b int ,@c varchar(50)
declare @tab table (nombrelivre int , numedit int)
insert into @tab
select count(numlive) , numedi
from livre
group by numedi
declare c cursor for select * from @tab
order by nombrelivre desc
open c
fetch next from c into @a,@b
set @c= (select e.nomedi
from editeur e
where e.numedi=@b)
close c
deallocate c
return @c
end
print dbo.q6ffun()
7- TRIGGERS :
a/
create trigger trga
on livre
for insert
as
begin
declare @a int
set @a=(select inserted.nbrexemlairedisponible from inserted)
if @a<=0
begin
Raiserror('on peut pas ajouter car le nombre d''exemplaire <=
0',15,120)
Rollback
end
end
b/
create trigger trgb
on adherent
for insert as
begin
declare @d date
set @d=(select dateNaissance from inserted)
if @d>'2010-12-12'
begin
raiserror ('age est inferieur à 7',1,16)
rollback
end
end
c/
create trigger trgc on emprunt
for update
as
begin
Raiserror('on peut pas ajouter',15,120)
rollback
end
d/
create trigger trgd
on emprunt
for insert
as
begin
declare @a int
set @a=(select numLiv from inserted)
update livre set
nbrexemlairedisponible=nbrexemlairedisponible-1
where livre.numlive=@a
end
e/
create trigger trge
on retour
for insert
as
begin
update livre set nbrexemlairedisponible=nbrexemlairedisponible+1
where livre.numlive in (select numLiv from emprunt
where numEmp in (select numEmp from inserted))
end
f/
create trigger trgf
on emprunt
for delete
as
begin
if exists (select * from deleted where numEmp in (
select numEmp from retour ))
begin
Raiserror('on peut pas supprimer',15,120)
rollback
end
else
begin
update livre set
nbrexemlairedisponible=nbrexemlairedisponible+1
where livre.numlive in
(select numLiv from deleted)
print 'le livre est modifié'
end
end
g/
create trigger trgg
on emprunt
for update
as
begin
declare @a int, @b int
set @a=(select numLiv from deleted)
set @b=(select numLiv from inserted)
if @a!=@b
begin
update livre set
nbrexemlairedisponible=nbrexemlairedisponible-1
where livre.numlive=@b
update livre set
nbrexemlairedisponible=nbrexemlairedisponible+1
where livre.numlive=@a
end
end
