#SQLQuery3.sql

2023-12-25 12:10:27

-- Test
use ProductManagementSystem

select * from tblProduct
select * from tblOrder
select * from tblOrderDetail
select * from tblUser

alter table tblOrderDetail
add constraint check_quantity check (Quantity > 0)
go

insert into tblOrderDetail (OrderID, ProductID, Quantity, Price)
values
(3, 3, -1, -1) -- inserted
go

-- Xu ly du lieu tu cau lenh insert, update, delete
---- Trigger (gan va insert, update, delete tren 1 bang nao do)
---- Ko cho phep chen du lieu quantity < 0 -> trong bang tblOrderDetail
create trigger trig_insert_tblOrderDetail on tblOrderDetail
for insert
as
begin
	if (select count(*) from inserted where Quantity < 0) > 0
	begin
		print N'Ko duoc phep chen du lieu quantity < 0'
		rollback transaction
	end
end

create trigger trig_insert_tblOrderDetail2 on tblOrderDetail
for insert
as
begin
	rollback transaction
end

alter trigger trig_insert_tblOrderDetail2 on tblOrderDetail
for insert
as
begin
	print N'Bi lam sao???'
	rollback transaction
end

---test
select * from tblOrderDetail

insert into tblOrderDetail (OrderID, ProductID, Quantity, Price)
values
(5, 1, -1, -1) -- inserted
go

insert into tblOrderDetail (OrderID, ProductID, Quantity, Price)
values
(5, 1, 1, 1) -- inserted
go

select * from tblOrderDetail

update tblOrderDetail set Quantity = -1 where OrderID = 1 and ProductID = 1
go

-- Tat ca cac ban ghi bi anh huong boi cau lenh update -> nam trong bien -> inserted
create trigger trigger_update_tblorderdetail on tblOrderDetail
for update
as
begin
	if(select count(*) from inserted where Quantity < 0) > 0
	begin
		print N'Khong dc phep sua Quantity < 0'
		rollback transaction
	end
end

-- Test
update tblOrderDetail set Quantity = -10 where OrderID = 1 and ProductID = 1
go

update tblOrderDetail set Quantity = -10 where OrderID = 1

-- Ko cho sua Column OrderId trong tblOrderDetail thi lam the nao
create trigger trigger_no_update_orderid on tblOrderDetail
for update
as
begin
	if update(OrderId)
	begin
		print N'Ko duoc sua column OrderId'
		rollback transaction
	end
end

update tblOrderDetail set OrderID = 2 where OrderID = 1

-- Delete
select * from tblUser

create trigger trigger_no_delete_user on tblUser
for delete
as
begin
	if (select count(*) from deleted where UserID in (1,2)) > 0
	begin
		print N'Khong duoc phep xoa nguoi dung 1, 2'
		rollback transaction
	end
end

delete from tblUser where UserID = 1
go

---- trigger: insert, update, delete
select * from tblProduct
select * from tblOrderDetail
select * from tblOrder

delete from tblProduct where ProductID = 5
go

delete from tblProduct where ProductID = 3
go

delete from tblOrderDetail where ProductID = 3

-- Co cach nao de chi chay cau lenh nhu sau
delete from tblProduct where ProductID = 1
go

create trigger trigger_instead_of_tblorderdetail on tblProduct
instead of delete
as
begin
	delete from tblOrderDetail where ProductID in (select ProductID from deleted)
	delete from tblProduct where ProductID in (select ProductID from deleted)
end

-- Co cach nao de chi chay cau lenh nhu sau
delete from tblProduct where ProductID = 1
go

select * from tblProduct

select * from tblProduct
where Quantity = 10
go

select * from tblProduct
where Quantity in (5, 10, 80)
go

select * from tblProduct
where Quantity not in (5, 10, 80)
go

-- Tim ra san pham -> Description chua tu "first"
select * from tblProduct
where Description like '%4K%'
go

select * from tblProduct
where Description like '4K%'
go

select * from tblProduct
where Description like '%4K'
go

select * from tblProduct
where Description like '%in%world%'
go

select * from tblProduct
where Description like '%world%in%'
go