select * from Student

create clustered index ci_firstname on Student (firstname)

create nonclustered index ci_lastname on Student (lastname)
create nonclustered index ci_address on Student (address)

-- Trigger
select * from Student2

alter table Student2
add gender nvarchar(16)

update Student2 set gender = 'Nam'

alter table Student2
add constraint check_gender check (gender in ('Nam', 'Nu'))

alter table Student2
drop constraint check_gender

--- Yeu cau: gender => Nam, Nu
--- Cai dat dieu kien gi de gender chi nhan gia tri Nam, Nu => ko insert cac gia tri khac
 insert into Student2(rollno, firstname, lastname, address, phone, email, age, gender)
 values
 ('R004', 'ABC', 'OKOK', 'Ha Noi', '12321312', 'okok123@gmail.com', 55, 'Xin Chao')

 delete from Student2 where rollno = 'R003'

 -- đề cập tới table => insert, update, delete => Mỗi một lệnh có thể gắn với 1 trigger
 ---- trigger insert vao bang Student2
 create trigger trigger_insert_student2 on Student2
 for insert
 as
 begin
	-- lenh kiem tra
	if (select gender from inserted) <> 'Nam' and (select gender from inserted) <> 'Nu'
	begin
		print N'Yeu cau du lieu nhap vao Nam hoac Nu'
		rollback transaction
	end
 end

 drop trigger trigger_insert_student2

 ----- trigger update cho bang Student2
 select * from Student2

 update Student2 set gender = 'XinChao' where rollno = 'R003'

 update Student2 set phone = '11111111' where rollno = 'R003'

 create trigger trigger_update_student2 on Student2
 for update
 as
 begin
	if (select gender from inserted) <> 'Nam' and (select gender from inserted) <> 'Nu'
	begin
		print N'Yeu cau du lieu nhap vao Nam hoac Nu'
		rollback transaction
	end
 end

 select * from Student2
 --- Ko cho phep xoa phan tu rollno = R000
 create trigger trigger_delete_student2 on Student2
 for delete
 as
 begin
	if (select rollno from deleted) = 'R000'
	begin
		print N'Khong dc xoa phan tu R000'
		rollback transaction
	end
 end
 
 delete from Student2 where rollno = 'R000'
 delete from Student2 where rollno = 'R003'

 --- Kiem soat du lieu : category, news
 select * from category
 select * from news

--- delete from news where id_category = 1
 delete from category where id = 1

 create trigger trigger_instead_of_delete_category on category
 instead of delete
 as
 begin
	delete from news where id_category in (select id from deleted)
	delete from category where id in (select id from deleted)
 end