shophere.sql_full.sql

NIIT project

create database ShopHere
go

use ShopHere
go

create table Items
(
	ItemID char(6) not null,
	ItemName varchar(10) not null,
	ItemDescription varchar(30) not null,
	CategoryID char(6) not null,
	UnitPrice money,
	ReorderLevel int,
	QOH int
)
go


alter table dbo.Items
add constraint pk_item primary key (ItemID)
go


create proc prc_genItems
@itemid char(6) output
as
if not exists(select * from dbo.Items ) select @itemid='000000'
else
	select @itemid=Max(RIGHT(ItemID,5)) from dbo.Items
	select @itemid=
	case
		when @itemid >=0 and @itemid <9 Then 'I0000'+CONVERT(char,@itemid+1)
		when @itemid >=9 and @itemid <99 Then 'I000'+CONVERT(char,@itemid+1)
		when @itemid >=99 and @itemid <999 Then 'I00'+CONVERT(char,@itemid+1)
		when @itemid >=999 and @itemid <9999 Then 'I0'+CONVERT(char,@itemid+1)
		when @itemid >=9999 and @itemid <=99998 Then 'I'+CONVERT(char,@itemid+1)
		
	end
go


CREATE proc prc_insert_Items
@iname varchar(10), @idescription varchar(30), @catid char(6), @price money, @rolevel int, @q int
as
begin
	declare @itemid char(6)
	exec dbo.prc_genItems @itemid output

	insert into dbo.Items (ItemID, ItemName, ItemDescription, CategoryID, UnitPrice, ReorderLevel, QOH)
		values (@itemid, @iname, @idescription, @catid, @price, @rolevel, @q)
end
go


create trigger trg_check_Items
on dbo.Items
for insert, update
as
begin
	declare @qoh int, @price money, @rol int
	select @qoh = QOH, @price = UnitPrice, @rol = ReOrderLevel
	from inserted
	
	if @qoh <= 0 or @price <= 0 or @rol <= 0
		begin
			print'Chu y: QOH, UnitPrice, ReOrderLevel > 0'
			rollback tran
		end
end
go
------------------------------------------------------
 

create table Categories		
(
	CategoryID char(6) not null,
	CatDescription varchar(30) not null
)
go


alter table dbo.Categories
add constraint pk_cats primary key (CategoryID)
go


create proc prc_genCats
@catid char(6) output
as
if not exists(select * from dbo.Categories ) select @catid='000000'
else
	select @catid=Max(RIGHT(CategoryID,5)) from dbo.Categories
	select @catid=
	case
		when @catid >=0 and @catid <9 Then 'C0000'+CONVERT(char,@catid+1)
		when @catid >=9 and @catid <99 Then 'C000'+CONVERT(char,@catid+1)
		when @catid >=99 and @catid <999 Then 'C00'+CONVERT(char,@catid+1)
		when @catid >=999 and @catid <9999 Then 'C0'+CONVERT(char,@catid+1)
		when @catid >=9999 and @catid <=99998 Then 'C'+CONVERT(char,@catid+1)
		
	end
go


CREATE proc prc_insert_Cats
@catdescription varchar(30)
as
begin
	declare @catid char(6)
	exec dbo.prc_genCats @catid output

	insert into dbo.Categories (CategoryID, CatDescription)
		values (@catid, @catdescription)
end
go
----------------------------------------------


create table ShippingMethods	
(
	ShippingMethodID char(6) not null,
	SM_Description varchar(30) not null
)
go


alter table dbo.ShippingMethods
add constraint pk_smethods primary key (ShippingMethodID)
go

create proc prc_genShippingMethods
@smid char(6) output
as
if not exists(select * from dbo.ShippingMethods ) select @smid='000000'
else
	select @smid=Max(RIGHT(ShippingMethodID,5)) from dbo.ShippingMethods
	select @smid=
	case
		when @smid >=0 and @smid <9 Then 'S0000'+CONVERT(char,@smid+1)
		when @smid >=9 and @smid <99 Then 'S000'+CONVERT(char,@smid+1)
		when @smid >=99 and @smid <999 Then 'S00'+CONVERT(char,@smid+1)
		when @smid >=999 and @smid <9999 Then 'S0'+CONVERT(char,@smid+1)
		when @smid >=9999 and @smid <=99998 Then 'S'+CONVERT(char,@smid+1)
		
	end
go


CREATE proc prc_insert_ShippingMethods
@smdescription varchar(30)
as
begin
	declare @smid char(6)
	exec dbo.prc_genShippingMethods @smid output

	insert into dbo.ShippingMethods (ShippingMethodID, SM_Description)
		values (@smid, @smdescription)
end
go
---------------------------------------


create table Suppliers		
(
	SupplierID char(6) not null,
	FirstName varchar(10) not null,
	LastName varchar(10) not null,
	Address varchar(50),
	City char(10) not null,
	Zip char(10),
	State char(10),
	Country char(3),
	Phone char(15) not null,
)
go


alter table dbo.Suppliers
add constraint pk_suppliers primary key (SupplierID)
go


create proc prc_genSuppliers
@sid char(6) output
as
if not exists(select * from dbo.Suppliers) select @sid='000000'
else
	select @sid=Max(RIGHT(SupplierID,5)) from dbo.Suppliers
	select @sid=
	case
		when @sid >=0 and @sid <9 Then 'U0000'+CONVERT(char,@sid+1)
		when @sid >=9 and @sid <99 Then 'U000'+CONVERT(char,@sid+1)
		when @sid >=99 and @sid <999 Then 'U00'+CONVERT(char,@sid+1)
		when @sid >=999 and @sid <9999 Then 'U0'+CONVERT(char,@sid+1)
		when @sid >=9999 and @sid <=99998 Then 'U'+CONVERT(char,@sid+1)
		
	end
go


create proc prc_insert_Suppliers 
@fname varchar(10), @lname varchar(10), @add varchar(50), @city char(10), @zip char(10), @state char(10), @country char(3), @phone char(15)
as
begin
	declare @sid char(6)
	exec dbo.prc_genSuppliers @sid output

	insert into dbo.Suppliers (SupplierID, FirstName, LastName, Address, City, Zip, State, Country, Phone )
		values (@sid, @fname, @lname, @add, @city, @zip, @state, @country, @phone)
end
go


create trigger trg_check_Suppliers
on dbo.Suppliers
for insert, update
as
begin
	declare @phone char(15)
	select @phone = Phone
	from inserted

	if (@phone not like ('[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9]'))
	begin
		print'Chu y: Phone phai co dinh dang la [0-9][0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9]'
		rollback tran
	end
end
go
----------------------------------------------------------


create table Employees					
(
	EmployeeID char(6) not null,
	FirstName varchar(10) not null,
	LastName varchar(10) not null,
	Title char(30) not null,
	Phone char (15) not null
)

go


alter table dbo.Employees
add constraint pk_emps primary key (EmployeeID)

go


create proc prc_genEmps
@empid char(6) output
as
if not exists(select * from dbo.Employees) select @empid='000000'
else
	select @empid=Max(RIGHT(EmployeeID,5)) from dbo.Employees
	select @empid=
	case
		when @empid >=0 and @empid <9 Then 'E0000'+CONVERT(char,@empid+1)
		when @empid >=9 and @empid <99 Then 'E000'+CONVERT(char,@empid+1)
		when @empid >=99 and @empid <999 Then 'E00'+CONVERT(char,@empid+1)
		when @empid >=999 and @empid <9999 Then 'E0'+CONVERT(char,@empid+1)
		when @empid >=9999 and @empid <=99998 Then 'E'+CONVERT(char,@empid+1)
		
	end

go


create proc prc_insert_Emps 
@fname varchar(10), @lname varchar(10), @title char(30), @phone char(15)
as
begin
	declare @empid char(6)
	exec dbo.prc_genEmps @empid output
	insert into dbo.Employees (EmployeeID, FirstName, LastName, Title, Phone )
		values (@empid, @fname, @lname, @title, @phone)
	
end
go


create trigger trg_check_Title_Emps
on dbo.Employees
for insert, update
as
begin
	declare @title char(30), @phone char(15)
	select @phone = Phone, @title = Title
	from inserted
	
	if	(@phone not like ('[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9]'))
	or	(@title not in ('Trainee','Floor Manager','Senior Manager','Floor In Charge','Excutive','Senior Excutive'))
		begin
			print'Kiem tra lai cac truong hop sau:'
			print'1- Title phai la : Trainee, Floor Manager, Senior Manager, Floor In Charge, Excutive, Senior Excutive'
			print'2- Phone phai thuoc dinh dang : [0-9][0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9]'
			rollback tran
		end	
end
go
---------------------------------------------------------


create table PurchaseOrders
(
	PurchaseOrderID char(4) not null,
	SupplierID char(6) not null,
	EmployeeID char(6) not null,
	OrderDate datetime,
	ShipDate datetime,
	ShippingMethodID char(6) not null,
	FreightCharge money
)
go


alter table dbo.PurchaseOrders
add constraint pk_purchaseorders primary key (PurchaseOrderID)
go


create trigger trg_check_PurchaseOrderID
on dbo.PurchaseOrders
for insert, update
as
begin
	declare @pid char(6)
	select @pid = PurchaseOrderID
	from inserted
	
	if @pid not like ('P[0-9][0-9][0-9]')
		begin
			print'Chu y: PurchaseOrderID phai co dinh dang la P[0-9][0-9][0-9]'
			rollback tran
		end
end
go