Nội dung buổi học
- Index
	- Xác định được trường dữ liệu nào trong bảng => được sử dụng trong các câu select => nhiều nhất => ít bị thay đổi.
	- Đánh index trên column đó => kinh nghiệm => đánh index
		- Ưu điểm : hệ thống chạy nhanh
		- Nhược điểm :
			- Chậm đi => tội tệ hơn
			- Tạo ra 1 bảng mapping <=> bảng chính
			- Đánh index => thay đổi dữ liệu => bảng index (mapping) => gen lại toàn bộ => ngốn tài nguyên nhất nhiều (update)

			Student => fullname => index

			create index index_student_fullname on Student (fullname)
- Trigger
	- Hiểu Trigger là gì
	- Các loại Trigger
		=> insert & update & delete
		=> instead of delete => Sử dụng trong TH xoá dữ liệu có foreign key.
	- Cách sử dụng
		- create trigger
		- alter trigger
		- drop trigger
	- Dữ liệu trong trigger
		- selected => chứa dữ liệu của insert & update
		- deleted => chưa dữ liệu trong lệnh delete (delete thường, instead)

-- Using StudentManagementSystem
use StudentManagementSystem
go

-- TEST
select * from Class
select * from Result
select * from Student
select * from Subject

alter table Student
add gender nvarchar(16)

insert into Student (StudentId, StudentName, BirthDate, ClassId, Gender)
values
(7, 'ABC', '1999-01-01', 1, 'Male')
go

-- Gender : Male, Female, Other => Nhiem => khong cho phep chen gia tri Gender ko hop
-- Trong TH => insert Gender khong hop ly => fail => ko cho insert

create trigger trigger_insert_check_gender_student on Student
for insert
as
begin
	if ((select gender from inserted) <> 'Male' 
		and (select gender from inserted) <> 'Female' 
		and (select gender from inserted) <> 'Other')
	begin
		print N'Gioi tinh khong chinh xac => chua cac gia tri Male, Female, Other'
		rollback transaction
	end
end

alter trigger trigger_insert_check_gender_student on Student
for insert
as
begin
	if (select COUNT(gender) from inserted where gender in ('Male', 'Female', 'Other')) = 0
	begin
		print N'Cach 2 >> Gioi tinh khong chinh xac => chua cac gia tri Male, Female, Other'
		rollback transaction
	end
end

drop trigger trigger_insert_check_gender_student

insert into Student (StudentId, StudentName, BirthDate, ClassId, Gender)
values
(10, 'ABC', '1999-01-01', 1, 'BBB')
go

select * from Student

update Student set Gender = 'Male' where StudentId = 9

create trigger trigger_update_check_gender_student on Student
for update
as
begin
	if (select COUNT(gender) from inserted where gender in ('Male', 'Female', 'Other')) = 0
	begin
		print N'Update >> Cach 2 >> Gioi tinh khong chinh xac => chua cac gia tri Male, Female, Other'
		rollback transaction
	end
end

update Student set Gender = 'ABC' where StudentId = 9 -- Failed
update Student set Gender = 'Male' where StudentId = 9 -- Success

select * from Student
update Student set StudentId = 1000 where StudentId = 9

update Student set StudentId = 9 where StudentId = 1000

-- khong muon cho sua column : StudentId
create trigger trigger_update_no_change_studentid_student on Student
for update
as
begin
	if UPDATE(studentid)
	begin
		print N'Khong duoc thay doi StudentId'
		rollback transaction
	end
end

update Student set StudentId = 1000 where StudentId = 9

select * from Student

delete from Student where StudentId = 7

create trigger trigger_delete_no_studentid_7_student on Student
for delete
as
begin
	if (select StudentId from deleted) = 7
	begin
		print N'Khong duoc xoa phan tu StudentId = 7'
		rollback transaction
	end
end

delete from Student where StudentId = 7
delete from Student where StudentId = 6

alter trigger trigger_delete_no_studentid_7_student on Student
for delete
as
begin
	if (select COUNT(StudentId) from deleted where StudentId between 1 and 7) > 0
	begin
		print N'Khong duoc xoa phan tu StudentId tu 1 -> 7'
		rollback transaction
	end
end

delete from Student where StudentId = 7

drop trigger trigger_delete_no_studentid_7_student

select * from Student

delete from Student where StudentId = 1

delete from Result where StudentId = 1
delete from Student where StudentId = 1

delete from Student where StudentId = 2

create trigger trigger_instead_of_delete_student on Student
instead of delete
as
begin
	delete from Result where StudentId in (select StudentId from deleted)
	delete from Student where StudentId in (select StudentId from deleted)
end

delete from Student where StudentId = 2

select * from Student