use C2010G

select * from Student
select * from Student where rollno like '%00%'

select * from Student, Marks
where Student.rollno = Marks.rollno

select * from orders

create clustered index index_order_no on orders (ord_no)

drop index index_order_no on orders

-------- Trigger
select * from Student

--- insert, update, delete => table => kich hoat khoi code len.
----- Moi mot trigger => gan voi 1 query insert, update, delete cua 1 bang (table)
----- Chi dc phep insert sinh vien co gioi tinh Nam | Nu
insert into Student(rollno, fullname, email, address, brithday, gender)
values
('R004', 'TRAN VAN D', 'd@gmail.com', 'ninh binh', '1996-06-02', 'OKOK')

insert into Student(rollno, fullname, email, address, brithday, gender)
values
('R002', 'TRAN VAN D', 'd@gmail.com', 'ninh binh', '1996-06-02', 'Nam')

delete from Student where rollno = 'R004'

alter table Student
add constraint check_gender check (gender = 'Nam' or gender = 'Nu')

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

alter table Student
drop constraint check_gender

-- Trigger Insert
create trigger trigger_insert_check_gender on Student
for insert
as
begin
	-- Khoi code se duoc kich hoat khi cau lenh insert tren bang Student duoc thu hien
	if (select count(*) from inserted where gender not in ('Nam', 'Nu')) > 0
	begin
		print N'Khong dc insert ban ghi co gender khac Nam|Nu'
		rollback transaction
	end
end

update Student set gender = 'OKOK' where rollno = 'R003'

select * from Student

alter trigger trigger_update_check_gender on Student
for update
as
begin
	print N'Goi toi trigger update check gender'
	-- Khoi code se duoc kich hoat khi cau lenh insert tren bang Student duoc thu hien
	if (select count(*) from inserted where gender not in ('Nam', 'Nu')) > 0
	begin
		print N'Khong dc update ban ghi co gender khac Nam|Nu'
		rollback transaction
	end
end

alter trigger trigger_not_update_rollno on Student
for update
as
begin
	print N'Goi toi trigger not update rollno'
	if update(rollno)
	begin
		print N'Not update rollno'
		rollback transaction
	end
end

create trigger trigger_okok on Student
for update
as
begin
	print N'trigger okok'
	rollback transaction
end

drop trigger trigger_okok

update Student set fullname = 'R007' where rollno = 'R001'

select * from Student

---- Chan khong cho phep xoa record: R001
delete from Student where rollno = 'R004'
delete from Student where rollno = 'R001'

create trigger trigger_not_delete_R001 on Student
for delete
as
begin
	if (select count(*) from deleted where rollno = 'R001') > 0
	begin
		print N'Not delete rollno: R001'
		rollback transaction
	end
end

---- instead of delete
alter table Marks
add constraint fk_student_marks foreign key (rollno) references Student(rollno)

select * from Student
select * from Marks
select * from Subject

delete from Marks where rollno = 'R002'
delete from Student where rollno = 'R002'

create trigger trigger_instead_of_student on Student
instead of delete
as
begin
	delete from Marks where rollno in (select rollno from deleted)
	delete from Student where rollno in (select rollno from deleted)
end

delete from Student where rollno = 'R002'