Bài tập - Xây dự hệ thống đưa đón học sinh bằng xe bus của Trường Học - Lập trình SQL Server

1 CAU LENH: Insert, update, delete -> cai dat -> kich hoat 1 block code len -> thuc hien 1 logic nao -> huy cau lenh insert/update/delete (trigger)

Ban co the tao 1 trigger = xac dinh lenh su dung (insert, update, delete) + ten bang thuc hien

trigger = insert + student

insert into student (.....)
values
(...),
(...)
...

-- Can viet 1 chuc nang -> khong cho phep insert du lieu hoc vien co ngay sinh < '2000-01-01' vao bang student ???
--> Bai toan: giai quyet van de nhu the nao???

Giai phap tot nhat: check for birthday -> student
Nhung:
	student -> 1 tgian -> ton tai rat nhieu records co birthday < '2000-01-01'

	-- A: 1999-01-01: check birthday >= 1999-01-01 => OK
	-- B: 2000-01-01: ??? ko su dung check dc nua
	-- C: 2015-01-01: ??? ko su dung check dc nua

	

-- Tao CSDL
create database BT1844
go

-- Kich hoat CSDL
use BT1844
go

-- Tao tables
create table Student (
	id int primary key identity(1,1),
	fullname nvarchar(50),
	address nvarchar(200),
	father_name nvarchar(50),
	father_phone nvarchar(20),
	mother_name nvarchar(50),
	mother_phone nvarchar(20),
	birthday date,
	gender nvarchar(16),
	position_id int
)
go

create table bus (
	id int primary key identity(1,1),
	bus_no nvarchar(20),
	type nvarchar(20),
	seats_num int,
	driver_id int
)
go

create table driver (
	id int primary key identity(1,1),
	fullname nvarchar(50),
	phone nvarchar(20),
	gender nvarchar(16),
	address nvarchar(200)
)
go

create table schedule (
	bus_id int not null,
	position_id int not null,
	primary key (bus_id, position_id)
)
go

create table position (
	id int primary key identity(1,1),
	address nvarchar(200)
)
go

-- Foreign Key
alter table student
add constraint fk_position foreign key (position_id) references position (id)
go

alter table bus
add constraint fk_driver foreign key (driver_id) references driver (id)
go

alter table schedule
add constraint fk_bus foreign key (bus_id) references bus (id)
go

alter table schedule
add constraint fk_position_schedule foreign key (position_id) references position (id)
go

-- Insert Data
insert into driver (fullname, gender, phone, address)
values
('LX A', 'Nam', '234234', 'Ha Noi'),
('LX B', 'Nam', '234234', 'Ha Noi'),
('LX C', 'Nam', '234234', 'Ha Noi'),
('LX D', 'Nam', '234234', 'Ha Noi'),
('LX E', 'Nam', '234234', 'Ha Noi')
go

insert into bus (driver_id, bus_no, type, seats_num)
values
(1, 'R001', 'VIP', 25),
(2, 'R002', 'VIP', 25),
(3, 'R003', 'VIP', 25),
(4, 'R004', 'VIP', 25),
(5, 'R005', 'VIP', 25)
go

insert into position (address)
values
('DC A'),
('DC B'),
('DC C'),
('DC D'),
('DC E')
go

insert into student (fullname, birthday, gender, address, father_name, father_phone, mother_name, mother_phone, position_id)
values
('HS A', '2016-02-16', 'Nam', 'Ha Noi', 'F A', '232434', 'M A', '343534', 1),
('HS B', '2016-02-16', 'Nam', 'Ha Noi', 'F B', '232434', 'M B', '343534', 1),
('HS C', '2016-02-16', 'Nam', 'Ha Noi', 'F C', '232434', 'M C', '343534', 2),
('HS D', '2016-02-16', 'Nam', 'Ha Noi', 'F D', '232434', 'M D', '343534', 3),
('HS E', '2016-02-16', 'Nam', 'Ha Noi', 'F E', '232434', 'M E', '343534', 3),
('HS F', '2016-02-16', 'Nam', 'Ha Noi', 'F F', '232434', 'M F', '343534', 4),
('HS G', '2016-02-16', 'Nam', 'Ha Noi', 'F G', '232434', 'M G', '343534', 5),
('HS H', '2016-02-16', 'Nam', 'Ha Noi', 'F H', '232434', 'M H', '343534', 5),
('HS I', '2016-02-16', 'Nam', 'Ha Noi', 'F I', '232434', 'M I', '343534', 3)
go

insert into schedule (bus_id, position_id)
values
(1, 1),
(1, 2),
(1, 3),
(2, 4),
(2, 5)
go

-- Query
---- 1) driver name (driver), bus no (bus), address (position)
select driver.fullname 'driver name', bus.bus_no, position.address
from driver, bus, position, schedule
where driver.id = bus.driver_id
	and position.id = schedule.position_id
	and bus.id = schedule.bus_id
go

create view view_schedule_bus
as
select driver.fullname 'driver name', bus.bus_no, position.address
from driver, bus, position, schedule
where driver.id = bus.driver_id
	and position.id = schedule.position_id
	and bus.id = schedule.bus_id
go

select * from view_schedule_bus

-- Xem thong tin sv theo bien so xe: ten hv (student), father name (student), father phone (student), dia diem don (position), xe don (bus), tai xe lai xe (driver)
select student.fullname 'Ten HV', student.father_name, student.father_phone, position.address, bus.bus_no, driver.fullname 'Driver Name'
from student, position, bus, driver, schedule
where student.position_id = position.id
	and position.id = schedule.position_id
	and schedule.bus_id = bus.id
	and bus.driver_id = driver.id
go

create proc proc_find_student_by_bus_no
	@busNo nvarchar(20)
as
begin
	select student.fullname 'Ten HV', student.father_name, student.father_phone, position.address, bus.bus_no, driver.fullname 'Driver Name'
	from student, position, bus, driver, schedule
	where student.position_id = position.id
		and position.id = schedule.position_id
		and schedule.bus_id = bus.id
		and bus.driver_id = driver.id
		and bus.bus_no = @busNo
end

exec proc_find_student_by_bus_no 'R001'
exec proc_find_student_by_bus_no 'R002'
exec proc_find_student_by_bus_no 'R003'

-- Xem thong tin: ten hoc vien (student), gioi tinh (student), dia chi dong (postition)
select student.fullname, student.gender, position.address
from student, position
where Student.position_id = position.id
go

--------------------------------------------------------------------------------------------
----- TRIGGER
select * from student

create trigger TG_check_birthday_insert_student on student
for insert
as
begin
	-- Goi xu ly block nay -> khi thuc hien insert du lieu vao bang student
	-- rollback transaction
	if (select count(*) from inserted where birthday < '2000-01-01') > 0
	begin
		print N'Ko duoc chen hoc vien co ngay sinh < 2000-01-01'
		rollback transaction
	end
end


insert into student (fullname, birthday, gender, address, father_name, father_phone, mother_name, mother_phone, position_id)
values
('HS II', '2016-02-16', 'Nam', 'Ha Noi', 'F A', '232434', 'M A', '343534', 1)


insert into student (fullname, birthday, gender, address, father_name, father_phone, mother_name, mother_phone, position_id)
values
('HS III', '1999-02-16', 'Nam', 'Ha Noi', 'F A', '232434', 'M A', '343534', 1)

-- sua trigger create -> alter
-- Xoa trigger
drop trigger TG_check_birthday_insert_student

-- update
select * from student

update student set birthday = '1999-02-12' where id = 10
go


create trigger TG_check_birthday_update_student on student
for update
as
begin
	-- Goi xu ly block nay -> khi thuc hien insert du lieu vao bang student
	-- rollback transaction
	if (select count(*) from inserted where birthday < '2000-01-01') > 0
	begin
		print N'Ko duoc update hoc vien co ngay sinh < 2000-01-01'
		rollback transaction
	end
end

update student set birthday = '1999-02-12' where id = 9

update student set fullname = 'AAA' where id < 3

create trigger TG_no_update_fullname_student on student
for update
as
begin
	if update(fullname)
	begin
		print 'Ko dc thay doi ten hoc vien'
		rollback transaction
	end
end

select * from student

-- Ko cho phep xoa ban ghi: 1, 2, 5, 6
delete from student where id >= 5

create trigger TG_no_delete_1_2_5_6_student on student
for delete
as
begin
	if (select count(*) from deleted where id in (1,2,5,6)) > 0
	begin
		print 'Ko dc xoa ban ghi 1, 2, 5, 6'
		rollback transaction
	end
end

----
select * from student
select * from position
select * from schedule
select * from bus
select * from driver

insert into driver (fullname, gender, phone, address)
values
('AA', 'Nam', '234234', 'Ha Noi')
go

delete from driver where id = 6

delete from driver where id = 5

delete from schedule where bus_id in (select id from bus where driver_id = 5)

delete from bus where driver_id = 5

create trigger TG_instead_of_delete_driver on driver
instead of delete
as
begin
	delete from schedule where bus_id in (select id from bus where driver_id in (select id from deleted))

	delete from bus where driver_id in (select id from deleted)

	delete from driver where id in (select id from deleted)
end

delete from driver where id = 4