Untitled

use master
go
create database QLsv1
go
use QLsv1

create table sv(
	masv char(10) not null primary key,
	hoten nvarchar(20),
	que nvarchar(20), 
	malop char(10) not null
)

create table lop(
	malop char(10) not null primary key,
	tenlop nvarchar(20),
	siso int
)

alter table sv add constraint fk_sv_lop foreign key(malop)
references lop(malop)

insert into lop values('L01', N'CNTT1', 80)
insert into lop values('L02', N'CNTT2', 45)
insert into lop values('L03', N'CNTT3', 78)


insert into sv values('sv01', N'Nguyen Van A', N'Ha Noi', 'L01')
insert into sv values('sv02', N'Nguyen Van B', N'Ha Noi', 'L02')
insert into sv values('sv03', N'Nguyen Van C', N'Ha Noi', 'L03')
insert into sv values('sv04', N'Nguyen Van D', N'Ha Nam', 'L01')
insert into sv values('sv05', N'Nguyen Van E', N'Ha Nam', 'L02')

select * from sv
select * from lop

--viet trigger insert sinh vien , moi khi insert sinh vien 
-- hay kiem tra xem malop co ton tai hay khong
-- siso co vuot qua 70 khong

create trigger trg_insertsv
on lop
for insert
as
begin
	declare @ml char(10)
	set @ml = (select malop from inserted)
	if(not exists(select * from lop where malop = @ml))
		begin
			raiserror(N'Khong ton tai ma lop nay', 16, 1)
			rollback transaction
		end
	else
		begin
			declare @sisos int
			set @sisos = (select siso from inserted)
			if(@sisos <70)
				begin
					raiserror(N'Khong thoa man', 16, 1)
					rollback transaction
				end
			else
				update lop set siso = siso +1 where malop = @ml
		end
end

insert into lop values('L04', N'CNTT1', 70)
select * from lop

--viet trigger update bang sinh vien thay doi malop cho sinh vien
-- kiem tra xem lop moi co >70 khong?
-- cap nhap lai si so lop cu -1, si so lop moi +1