create database bai5

create table sach

(Masach char(10) primary key

,Tensach nvarchar(30) not null

,NXB nvarchar(30)

,NamXB int

,Tentg nvarchar(30)

,Soluong int)

create table docgia

(Madg char(10) primary key

,Tendg nvarchar(30) not null

,Diachidg nvarchar(30))

create table muon_tra

(Madg char(10) not null

,Masach char(10) not null

,ngaymuon datetime not null

,SL int not null

,ngayhentra datetime not null

,ngaytra datetime

,constraint khoachinh primary key (Madg,Masach)

,constraint kn1 foreign key(Masach)references sach(Masach)

,constraint kn2 foreign key(Madg)references docgia(Madg))

create view v1

as

select *

from docgia

where madg in(select madg

              from muon_tra

              where datediff(dd,ngayhentra,getdate())>0 and ngaytra is null)

create view v2

as

select *

from sach

where Masach not in (select Masach

                     from muon_tra)

create view v3

as

select Masach,tensach,tentg,soluong

from sach

where masach in(select masach

                from muon_tra

                where ngaytra is null)

create proc t1

@madg char(30)

as

select *

from sach

where Masach in (select Masach

                from muon_tra

                where madg=@madg and ngaytra is null)

exec t1 '1a2'

create proc t2

@ngaymuon datetime

as

select *

from docgia

where Madg in(select Madg

              from muon_tra

              where Ngaymuon=@ngaymuon)

exec t2 '4/3/2011'

create proc t3

as

select *

from docgia

where Madg in(select Madg

              from muon_tra

              where datediff(dd,ngayhentra,getdate())=0)

exec t3

create trigger kt1

on muon_tra

for insert

as

if (select SL

    from inserted)<0 

begin

print N'dữ liệu nhập không hợp lệ'

rollback tran

end

else print N'dữ liệu nhập thành công'

insert into muon_tra

values ('1a2','a5','3/29/2011',-2,'4/10/2011',null)

create trigger kt2

on muon_tra

for insert

as

if (select SL

    from inserted)>(select soluong

                    from sach,inserted

                    where sach.masach=inserted.masach) 

begin

print N'dữ liệu nhập không hợp lệ'

rollback tran

end

else 

begin

print N'dữ liệu nhập thành công'

select * from inserted

update sach

set sach.soluong=sach.soluong-inserted.SL

from inserted

where sach.masach=inserted.masach

end

insert into muon_tra

values ('1a2','a5','3/29/2011',1,'4/10/2011',null)

alter table muon_tra

enable trigger kt1

declare nhap cursor

for 

select *

from docgia

where Madg in(select Madg

              from muon_tra

              where ngaytra is null)

declare @Madg char(10),@tendg nvarchar(30),@Diachidg nvarchar(30)

open nhap

print cast(N'Mã DG' as nchar(10))+cast(N'Tên DG' as nchar(30))+cast(N'Địa chỉ DG' as nchar(30))

fetch next from nhap

into @Madg,@tendg,@Diachidg

while @@fetch_status=0

begin

print cast(@Madg as nchar(10))+cast(@tendg as nchar(30))+cast(@Diachidg as nchar(30))

fetch next from nhap

into @Madg,@tendg,@Diachidg

end

close nhap