--8.
create
--alter
function uf_TinhTienPhat(@mapm varchar(10),@isbn varchar(12), @masach varchar(15))
returns float
as
begin
	declare @ngaymuon date = (select ngaymuon from phieumuon where mapm = @mapm)
	declare @songayqd int = (select songayquydinh from CT_PhieuMuon
							where isbn = @isbn and masach = @masach and
							mapm = @mapm)
	return (select mucgiaphat * (case when (datediff(day,@ngaymuon,ngaytra) > @songayqd) then (datediff(day,@ngaymuon,ngaytra) - @songayqd)
								else 0 end)
								from PhieuTra pt, CT_PhieuTra ctt
	where pt.mapm = @mapm and ctt.mapt = pt.mapt and
	ctt.isbn = @isbn and ctt.masach = @masach)
end
go
--pm:u(ngaymuon)
--pt:u(mapm,ngaytra)
--ctm:u(songayquidinh)
--ctpt:i(+) d(-) u(mucgiaphat)
create
--alter
function uf_LayTienPhat(@mapm varchar(10),@isbn varchar(12), @masach varchar(15))
returns float
as
begin
	return (select tienphat from CT_PhieuTra ct join PhieuTra pt on ct.mapt = pt.mapt
			where ct.masach = @masach and ct.isbn = @isbn and pt.mapm = @mapm)
end
go
select dbo.uf_LayTienPhat('Pm004','116525441','S001')
select dbo.uf_TinhTienPhat('Pm004','116525441','S001')
create
--alter
trigger utr8 on phieumuon
for update
as
	if not exists (select * from inserted i join CT_PhieuMuon ct on ct.mapm = i.mapm
			where dbo.uf_TinhTienPhat(ct.mapm,ct.isbn,ct.masach) = dbo.uf_LayTienPhat(ct.mapm,ct.isbn,ct.masach))
	begin
		raiserror ('r8',16,1)
		rollback tran
	end
go