By GokiSoft.com|
15:19 02/12/2020|
SQL Server/MySQL
[Share Code] Tìm hiểu index, trigger trong lập trình SQL Server
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)
endTags:
Phản hồi từ học viên
5
(Dựa trên đánh giá ngày hôm nay)
![Đỗ Minh Quân [T2008A]](https://www.gravatar.com/avatar/fa40264d7c4b4209c87a9e9451d2b9f0.jpg?s=80&d=mm&r=g){kind=avatar}
Đỗ Minh Quân
2020-12-09 16:10:07
create database quan_ly_diem_danh_app
use quan_ly_diem_danh_app
create table studentss(
id int identity (1,1) primary key,
rollno nvarchar (100),
fullname nvarchar (100),
sex nvarchar (50),
birthday date,
country nvarchar (100),
email nvarchar (100),
sdt nvarchar (50),)
create table teachers(
id int identity (1,1) primary key,
code_teacher nvarchar (100),
fullname_teacher nvarchar (100),
sex_teacher nvarchar (100),
birthday_teacher date ,
)
create table subss(
id int identity (1,1) primary key,
code_sub nvarchar (50),
name_sub nvarchar (50),
total nvarchar (100),
)
create table classs(
id int identity (1,1) primary key,
code_class nvarchar (100),
name_class nvarchar (100),
note nvarchar (100),)
create table classmembers(
id int identity (1,1) primary key,
rollno_member nvarchar (100),
classno nvarchar (100),
joindate date,
outdate date,
)
create table Schedules(
id int identity (1,1) primary key,
code_teach nvarchar (50),
code_sub nvarchar (50),
start_sub date,
finish_sub date,)
create table Attendances(
ScheduleId int,
id int identity (1,1) primary key,
rollno_Attendance nvarchar (100),
date_Attendance date,
Attendance_1 nvarchar (100),
Attendance_2 nvarchar (100),
note nvarchar (100),)
delete from studentss
delete from Teachers
delete from subss
delete from classs
delete from classmembers
delete from Schedules
delete from Attendances
insert into studentss
values
('roo1','do minh quan','nam','2002-09-26','hai phong','dominhquan260902@gmail.com','0586543018'),
('roo2','do minh quan','nam','2002-09-25','hai phong','dominhquan260902@gmail.com','0586543018'),
('roo3','do minh quan','nam','2002-09-24','hai phong','dominhquan260902@gmail.com','0586543018'),
('roo4','do minh quan','nam','2002-09-23','hai phong','dominhquan260902@gmail.com','0586543018'),
('roo5','do minh quan','nam','2002-09-22','hai phong','dominhquan260902@gmail.com','0586543018')
insert into Teachers
values
('1','tran van diep','nam','1929-08-10'),
('2','tran van a','nam','1929-08-9'),
('3','tran van b','nam','1929-08-8'),
('4','tran van c','nam','1929-08-7'),
('5','tran van d','nam','1929-08-6')
insert into subss
values
('c001','toan','10'),
('c001','toan','10'),
('c001','toan','10'),
('c001','toan','10'),
('c001','toan','10')
insert into classs
values
('c001','lop hoc adc','khong co hoc sinh'),
('c001','lop hoc adc','khong co hoc sinh'),
('c001','lop hoc adc','khong co hoc sinh'),
('c001','lop hoc adc','khong co hoc sinh'),
('c001','lop hoc adc','khong co hoc sinh')
insert into classmembers
values
('roo1','lop1','2020-01-2','2020-05-2'),
('roo2','lop1','2020-01-2','2020-05-2'),
('roo3','lop1','2020-01-2','2020-05-2'),
('roo4','lop1','2020-01-2','2020-05-2'),
('roo5','lop1','2020-01-2','2020-05-2')
insert into Schedules
values
('t001','e001','2020-09-10','2021-09-10'),
('t001','e001','2020-09-10','2021-09-10'),
('t001','e001','2020-09-10','2021-09-10'),
('t001','e001','2020-09-10','2021-09-10'),
('t001','e001','2020-09-10','2021-09-10')
insert into Attendances
values
('1','d1','2020-12-9','vang','du','rat la muon hoc'),
('2','d1','2020-12-9','vang','du','rat la muon hoc'),
('3','d1','2020-12-9','vang','du','rat la muon hoc'),
('4','d1','2020-12-9','vang','du','rat la muon hoc'),
('5','d1','2020-12-9','vang','du','rat la muon hoc')
--yeu cau select thong tin hoc vien trong moi lop hoc - dau vao la ten lop
create proc proc_yeu_cau_de_bai_1_1
@idclass nvarchar (100)
as
begin
select studentss.rollno, studentss.fullname , studentss.sex ,studentss.sdt , studentss.country ,studentss.email ,studentss.birthday
from studentss left join classs on studentss.rollno = classmembers.rollno_member
where classs.name_class = @idclass
end
exec proc_yeu_cau_de_bai_1_1
select Attendances.ScheduleId , Attendances.rollno_Attendance , Attendances.date_Attendance, Attendances.Attendance_1 , Attendances.Attendance_2 , Attendances.note ,
subss.code_sub , subss.name_sub , subss.total
from Attendances , subss left join Schedules on Attendances.id=Schedules.id
where classs.code_class=@codeclass
classs.name_class=@nameclass
create proc proc_yeu_cau_de_bai_1_2
@codeclass nvarchar (100)
@nameclass nvarchar (100)
as
begin
select Attendances.ScheduleId , Attendances.rollno_Attendance , Attendances.date_Attendance, Attendances.Attendance_1 , Attendances.Attendance_2 , Attendances.note ,
subss.code_sub , subss.name_sub , subss.total
from Attendances , subss left join Schedules on Attendances.id=Schedules.id
where classs.code_class=@codeclass
classs.name_class=@nameclass
end
exec proc_yeu_cau_de_bai_1_2
create trigger trigger_delete_stu on studentss
for delete
as
begin
delete from studentss
where rollno=1
print N'ban da nhap sai du lieu xin hay nhap du lieu dung'
rollback transaction
end 
