-- Active database
use C2010L

-- TEST
select * from Student

select * from Student where student_name like '%AN%'

-- Deploy - live: 1K record, 10K record, 100K record, 1T record, 1 ty record -> select -> chay cham -> khi dua ra ket qua.
-- Index: tang toc tim kiem record trong select
---- Index
------- clustered index: trong 1 bang chi dc phep danh index tren 1 column
------- nonclustered index: tao ra nhieu hon 1 lan trong table
create nonclustered index index_student_name on Student (student_name)

drop index index_student_name on Student

-- Trigger
---- TEST
select * from Student
select * from Subject
select * from Marks

insert into Student(roll_number, student_name, email, address, phone)
values
('R007', 'TRAN VAN K', 'K@GMAIL.COM', 'HA NOI', '12342423')

delete from Student where roll_number = 'R007'

-- Khi insert email khong co '@' => khong cho phep insert thanh cong.
---- Chung ta co the su dung check => kiem tra dieu kien tren
alter table Student
add constraint check_email check (email like '%@%')

alter table Student
drop constraint check_email

---- Chung ta cung co the su dung trigger de lam viec nay
------ Trigger: gan voi tung cau lenh insert, update, delete. Nghia la khi thuc hien cau len insert, update hoac delete => tu dong goi trigger tuong ung len de thuc thi
create trigger trigger_insert_check_email on Student
for insert
as
begin
	print 'Trigger insert bang Student duoc chay'
	-- doan nay code gi cung dc -> tuy thuoc vao nghiep vu cua bai toan.
end

alter trigger trigger_insert_check_email on Student
for insert -- for update, for delete
as
begin
	print 'Trigger insert bang Student duoc chay'
end

insert into Student(roll_number, student_name, email, address, phone)
values
('R007', 'TRAN VAN K', 'KGMAIL.COM', 'HA NOI', '12342423')

delete from Student where roll_number = 'R006'
select * from Student

-- Ap dung trigger trong bai toan kiem tra email co chua ky tu @ khong
alter trigger trigger_insert_check_email on Student
for insert -- for update, for delete
as
begin
	if (select count(*) from inserted where email not like '%@%') > 0
	begin
		print 'Khong duoc phep update email khong co ky tu @'
		rollback transaction -- Huy lenh insert -> khong cho thuc hien thanh cong.
	end
end

update Student set email = 'degmail.com' where roll_number = 'R005'
select * from Student

-- Viet trigger cho update
-- Ap dung trigger trong bai toan kiem tra email co chua ky tu @ khong
create trigger trigger_update_check_email on Student
for update
as
begin
	-- inserted: chua toan bo ban ghi trong lenh insert va update
	if (select count(*) from inserted where email not like '%@%') > 0
	begin
		print 'Khong duoc phep update email khong co ky tu @'
		rollback transaction -- Huy lenh insert -> khong cho thuc hien thanh cong.
	end
end
drop trigger trigger_update_check_email

update Student set email = 'egmail.com' where roll_number = 'R005'
select * from Student

-- Trong TH -> khong cho phep sua du lieu tren 1 column thi lam the nao.
---- Khong muon cho sua noi dung column roll_number
---- roll_number: primary key -> khong thay doi no.
update Student set roll_number = 'R006' where roll_number = 'R005'
select * from Student

create trigger trigger_not_update_roll_number on Student
for update
as
begin
	-- inserted: chua toan bo ban ghi trong lenh insert va update
	if update(roll_number)
	begin
		print 'Khong duoc phep sua noi dung column roll_number'
		rollback transaction -- Huy lenh insert -> khong cho thuc hien thanh cong.
	end
end

update Student set roll_number = 'R005' where roll_number = 'R009'
select * from Student

-- Khong cho phep xoa noi dung 2 bang ghi co roll_number la R001 va R002 thi lam the nao
create trigger trigger_not_delete_roll_number_R001_R002 on Student
for delete
as
begin
	-- inserted: chua toan bo ban ghi trong lenh insert va update
	if (select count(*) from deleted where roll_number in ('R006', 'R007')) > 0
	begin
		print 'Khong duoc phep xoa record co roll_number R006 & R007'
		rollback transaction -- Huy lenh insert -> khong cho thuc hien thanh cong.
	end
end

delete from Student where roll_number = 'R001'

---- TEST
select * from Student
select * from Subject
select * from Marks

insert into Marks(subject_id, roll_number, marks)
values
(3, 'R001', 5),
(4, 'R001', 8)

delete from Marks where roll_number = 'R003'
delete from Student where roll_number = 'R001'

create trigger trigger_delete_foreign_key on Student
instead of delete
as
begin
	-- inserted: chua toan bo ban ghi trong lenh insert va update
	delete from Marks where roll_number in (select roll_number from deleted)
	delete from Student where roll_number in (select roll_number from deleted)
end

drop trigger trigger_delete_foreign_key

delete from Student where roll_number = 'R001'