select * from SinhVien

select * from SinhVien where TenSinhVien like '%V%' --nhanh: 80-90 %

-- insert, update, delete: 10% (2-3%)

create clustered index IX_SinhVien on SinhVien (MaSinhVien)
drop index IX_SinhVien on SinhVien

--------------------------------------------------------------------------
------------------------------- Trigger ----------------------------------
--------------------------------------------------------------------------
select * from SinhVien
select * from MonHoc
select * from Course

select * from SinhVien

alter table SinhVien
add birthday date

alter table SinhVien
add gender nvarchar(20)

-- gender: Male, Female
update SinhVien set gender = 'Male'

update SinhVien set gender = 'DDD' where MaSinhVien = 'DDD'

insert into SinhVien(MaSinhVien, TenSinhVien, NgaySinh, gender)
values
('AA', 'Xin Chao', '1999-01-02', 'OKOK')

alter table SinhVien
add constraint check_gender check (gender = 'Male' or gender = 'Female')

alter table SinhVien
drop constraint check_gender

delete from SinhVien where MaSinhVien in ('AA', 'DTD')

update SinhVien set gender = 'DDD' where MaSinhVien = 'DDD'

insert into SinhVien(MaSinhVien, TenSinhVien, NgaySinh, gender)
values
('FSD', 'Xin Chao', '1999-01-02', 'Male')

select * from SinhVien
-- Trigger
create trigger trigger_check_gender on SinhVien
for insert
as
begin
	-- Khi cau lenh insert duoc thuc hien -> goi toi khoi code nay de kiem tra
	-- du lieu dc insert vao no nam trong bien inserted
	if (select count(*) from inserted where gender not in ('Male', 'Female')) > 0
	begin
		-- Huy lenh di
		print N'Gioi tinh phai la cac gia tri Male hoac Female'
		rollback transaction
	end
end

alter trigger trigger_check_gender on SinhVien
for insert
as
begin
	-- Khi cau lenh insert duoc thuc hien -> goi toi khoi code nay de kiem tra
	-- du lieu dc insert vao no nam trong bien inserted
	-- if (select count(*) from inserted where gender not in ('Male', 'Female')) > 0
	-- begin
		-- Huy lenh di
		print N'Gioi tinh phai la cac gia tri Male hoac Female'
		rollback transaction
	-- end
end

drop trigger trigger_check_gender

-- update
update SinhVien set gender = 'Female' where MaSinhVien = 'DDD'
--> Tat ca cac ban ghi bi anh huong => inserted

select * from SinhVien

create trigger trigger_update_check_gender on SinhVien
for update
as
begin
	-- Khi cau lenh insert duoc thuc hien -> goi toi khoi code nay de kiem tra
	-- du lieu dc insert vao no nam trong bien inserted
	if (select count(*) from inserted where gender not in ('Male', 'Female')) > 0
	begin
		-- Huy lenh di
		print N'Gioi tinh phai la cac gia tri Male hoac Female'
		rollback transaction
	end
end

-- Viet 1 trigger => khong cho phep thay doi MaSinhVien
update SinhVien set MaSinhVien = '123' where MaSinhVien = 'AA'
update SinhVien set TenSinhVien = 'Xin Chao' where MaSinhVien = 'AA'
select * from SinhVien

create trigger trigger_not_update_msv on SinhVien
for update
as
begin
	print N'Khong dc phep thay doi MSV'
	rollback transaction
end

alter trigger trigger_not_update_msv on SinhVien
for update
as
begin
	if update(MaSinhVien)
	begin
		print N'Khong dc phep thay doi MSV'
		rollback transaction
	end
end

-- trigger delete
select * from SinhVien

delete from SinhVien where MaSinhVien like '*A*' -- du lieu xoa nam trong bien deleted
delete from SinhVien where MaSinhVien = 'AA' -- du lieu xoa nam trong bien deleted
delete from SinhVien where MaSinhVien = 'dd' -- du lieu xoa nam trong bien deleted

create trigger trigger_not_delete_msv_aa on SinhVien
for delete
as
begin
	if (select count(*) from deleted where MaSinhVien = 'AA') > 0
	begin
		print N'Khong dc phep xoa sinh vien vs msv = AA'
		rollback transaction
	end
end

-------------------------------------------------------------------------
select * from MonHoc
select * from Course

delete from GiangVien
delete from SinhVien_Hoc_Course

delete from MonHoc where MaMonHoc = 'HTML' --> Co cach nao de chay dc cau lenh nay ko???
delete from MonHoc where MaMonHoc = 'AA'

insert into MonHoc (MaMonHoc, TenMonHoc)
values
('AA', 'AAAAAAAAAAAAAAAAAAAA')

delete from Course where Ma_MonHoc = 'HTML' -- OK

alter trigger trigger_instead_of_mmh on MonHoc
instead of delete
as
begin
	delete from Course where Ma_MonHoc in (select MaMonHoc from deleted)
	delete from MonHoc where MaMonHoc in (select MaMonHoc from deleted)
end