/*
SCRIPT FILE : 3 : Script Generated To Add Indexing And Relationships, Migration Upsizing Tool
copyright asc associates 2007, All rights Reserved
This is the script can be run either before or after data has been migrated to the SQL Server and creates the indexing and relationships.
*/


-- ENSURE YOU ARE IN THE CORRECT DATABASE TO EXECUTE THE SCRIPT
USE [Northwind]
GO



PRINT 'Adding Relationship [relProductsCategories]'
ALTER TABLE [Products] ADD CONSTRAINT [relProductsCategories]  FOREIGN KEY ([CategoryID]) REFERENCES [Categories]([CategoryID])
GO
PRINT 'Adding Relationship [relOrdersEmploees]'
ALTER TABLE [Orders] ADD CONSTRAINT [relOrdersEmploees]  FOREIGN KEY ([EmployeeID]) REFERENCES [Employees]([EmployeeID])
GO
PRINT 'Adding Relationship [relOrderDetailsProducts]'
ALTER TABLE [Order Details] ADD CONSTRAINT [relOrderDetailsProducts]  FOREIGN KEY ([ProductID]) REFERENCES [Products]([ProductID])
GO
PRINT 'Adding Relationship [relProductsSuppliers]'
ALTER TABLE [Products] ADD CONSTRAINT [relProductsSuppliers]  FOREIGN KEY ([SupplierID]) REFERENCES [Suppliers]([SupplierID])
GO
PRINT 'Adding Relationship [relOrdersCustomers]'
ALTER TABLE [Orders] ADD CONSTRAINT [relOrdersCustomers]  FOREIGN KEY ([CustomerID]) REFERENCES [Customers]([CustomerID]) ON UPDATE CASCADE 
GO
PRINT 'Adding Relationship [relOrderDetailsOrders]'
ALTER TABLE [Order Details] ADD CONSTRAINT [relOrderDetailsOrders]  FOREIGN KEY ([OrderID]) REFERENCES [Orders]([OrderID]) ON DELETE CASCADE 
GO
PRINT 'Adding Relationship [relOrdersShippers]'
ALTER TABLE [Orders] ADD CONSTRAINT [relOrdersShippers]  FOREIGN KEY ([ShipVia]) REFERENCES [Shippers]([ShipperID])
GO
PRINT 'Adding Check [chkEmployeesBirthDate] WITH NOCHECK'
ALTER TABLE [Employees] WITH NOCHECK ADD CONSTRAINT [chkEmployeesBirthDate]  CHECK ([BirthDate] <GetDate())
GO
PRINT 'Adding Check [chkOrderDetailsUnitPrice] WITH NOCHECK'
ALTER TABLE [Order Details] WITH NOCHECK ADD CONSTRAINT [chkOrderDetailsUnitPrice]  CHECK ([UnitPrice] >=0)
GO
PRINT 'Adding Check [chkOrderDetailsQuantity] WITH NOCHECK'
ALTER TABLE [Order Details] WITH NOCHECK ADD CONSTRAINT [chkOrderDetailsQuantity]  CHECK ([Quantity] >0)
GO
PRINT 'Adding Check [chkOrderDetailsDiscount] WITH NOCHECK'
ALTER TABLE [Order Details] WITH NOCHECK ADD CONSTRAINT [chkOrderDetailsDiscount]  CHECK ([Discount] Between 0 And 1)
GO
PRINT 'Adding Check [chkProductsUnitPrice] WITH NOCHECK'
ALTER TABLE [Products] WITH NOCHECK ADD CONSTRAINT [chkProductsUnitPrice]  CHECK ([UnitPrice] >=0)
GO
PRINT 'Adding Check [chkProductsUnitsInStock] WITH NOCHECK'
ALTER TABLE [Products] WITH NOCHECK ADD CONSTRAINT [chkProductsUnitsInStock]  CHECK ([UnitsInStock] >=0)
GO
PRINT 'Adding Check [chkProductsUnitsOnOrder] WITH NOCHECK'
ALTER TABLE [Products] WITH NOCHECK ADD CONSTRAINT [chkProductsUnitsOnOrder]  CHECK ([UnitsOnOrder] >=0)
GO
PRINT 'Adding Check [chkProductsReorderLevel] WITH NOCHECK'
ALTER TABLE [Products] WITH NOCHECK ADD CONSTRAINT [chkProductsReorderLevel]  CHECK ([ReorderLevel] >=0)
GO


PRINT 'Creating Trigger : TRAuditCategories'
GO
CREATE TRIGGER TRAuditCategories ON [Categories]
FOR UPDATE AS 
UPDATE [Categories]
 SET [A_CategoriesLastUpdatedOn] = GetDate(), [A_CategoriesLastUpdatedBy] = SUSER_SNAME()
FROM  [Categories] s
INNER JOIN [INSERTED] i 
ON s.[CategoryID] = i.[CategoryID]
GO


PRINT 'Creating Trigger : TRAuditCustomers'
GO
CREATE TRIGGER TRAuditCustomers ON [Customers]
FOR UPDATE AS 
UPDATE [Customers]
 SET [A_CustomersLastUpdatedBy] = SUSER_SNAME(), [A_CustomersLastUpdatedOn] = GetDate()
FROM  [Customers] s
INNER JOIN [INSERTED] i 
ON s.[CustomerID] = i.[CustomerID]
GO


PRINT 'Creating Trigger : TRAuditEmploees'
GO
CREATE TRIGGER TRAuditEmploees ON [Employees]
FOR UPDATE AS 
UPDATE [Employees]
 SET [A_EmployeesLastUpdatedBy] = SUSER_SNAME(), [A_EmployeesLastUpdatedOn] = GetDate()
FROM  [Employees] s
INNER JOIN [INSERTED] i 
ON s.[EmployeeID] = i.[EmployeeID]
GO


PRINT 'Creating Trigger : TRAuditOrderDetails'
GO
CREATE TRIGGER TRAuditOrderDetails ON [Order Details]
FOR UPDATE AS 
UPDATE [Order Details]
 SET [A_OrderDetailsLastUpdatedOn] = GetDate(), [A_OrderDetailsLastUpdatedBy] = SUSER_SNAME()
FROM  [Order Details] s
INNER JOIN [INSERTED] i 
ON s.[OrderID] = i.[OrderID] AND s.[ProductID] = i.[ProductID]
GO


PRINT 'Creating Trigger : TRAuditOrders'
GO
CREATE TRIGGER TRAuditOrders ON [Orders]
FOR UPDATE AS 
UPDATE [Orders]
 SET [A_OrdersLastUpdatedOn] = GetDate(), [A_OrdersLastUpdatedBy] = SUSER_SNAME()
FROM  [Orders] s
INNER JOIN [INSERTED] i 
ON s.[OrderID] = i.[OrderID]
GO


PRINT 'Creating Trigger : TRAuditProducts'
GO
CREATE TRIGGER TRAuditProducts ON [Products]
FOR UPDATE AS 
UPDATE [Products]
 SET [A_ProductsLastUpdatedBy] = SUSER_SNAME(), [A_ProductsLastUpdatedOn] = GetDate()
FROM  [Products] s
INNER JOIN [INSERTED] i 
ON s.[ProductID] = i.[ProductID]
GO


PRINT 'Creating Trigger : TRAuditShippers'
GO
CREATE TRIGGER TRAuditShippers ON [Shippers]
FOR UPDATE AS 
UPDATE [Shippers]
 SET [A_ShippersLastUpdatedBy] = SUSER_SNAME(), [A_ShippersLastUpdatedOn] = GetDate()
FROM  [Shippers] s
INNER JOIN [INSERTED] i 
ON s.[ShipperID] = i.[ShipperID]
GO


PRINT 'Creating Trigger : TRAuditSuppliers'
GO
CREATE TRIGGER TRAuditSuppliers ON [Suppliers]
FOR UPDATE AS 
UPDATE [Suppliers]
 SET [A_SuppliersLastUpdatedOn] = GetDate(), [A_SuppliersLastUpdatedBy] = SUSER_SNAME()
FROM  [Suppliers] s
INNER JOIN [INSERTED] i 
ON s.[SupplierID] = i.[SupplierID]
GO
PRINT 'Creating Index [idxCategoriesCategoryNameUnique]'
CREATE  UNIQUE NONCLUSTERED INDEX [idxCategoriesCategoryNameUnique] ON [Categories] ([CategoryName])
GO
PRINT 'Creating Index [idxCustomersCity]'
CREATE NONCLUSTERED INDEX [idxCustomersCity] ON [Customers] ([City])
GO
PRINT 'Creating Index [idxCustomersCompanyName]'
CREATE NONCLUSTERED INDEX [idxCustomersCompanyName] ON [Customers] ([CompanyName])
GO
PRINT 'Creating Index [idxCustomersPostalCode]'
CREATE NONCLUSTERED INDEX [idxCustomersPostalCode] ON [Customers] ([PostalCode])
GO
PRINT 'Creating Index [idxCustomersRegion]'
CREATE NONCLUSTERED INDEX [idxCustomersRegion] ON [Customers] ([Region])
GO
PRINT 'Creating Index [idxEmploeesLastName]'
CREATE NONCLUSTERED INDEX [idxEmploeesLastName] ON [Employees] ([LastName])
GO
PRINT 'Creating Index [idxEmploeesPostalCode]'
CREATE NONCLUSTERED INDEX [idxEmploeesPostalCode] ON [Employees] ([PostalCode])
GO
PRINT 'Creating Index [idxOrderDetailsOrderID]'
CREATE NONCLUSTERED INDEX [idxOrderDetailsOrderID] ON [Order Details] ([OrderID])
GO
PRINT 'Creating Index [idxOrderDetailsProductID]'
CREATE NONCLUSTERED INDEX [idxOrderDetailsProductID] ON [Order Details] ([ProductID])
GO
PRINT 'Creating Index [idxOrdersCustomerID]'
CREATE NONCLUSTERED INDEX [idxOrdersCustomerID] ON [Orders] ([CustomerID])
GO
PRINT 'Creating Index [idxOrdersEmployeeID]'
CREATE NONCLUSTERED INDEX [idxOrdersEmployeeID] ON [Orders] ([EmployeeID])
GO
PRINT 'Creating Index [idxOrdersOrderDate]'
CREATE NONCLUSTERED INDEX [idxOrdersOrderDate] ON [Orders] ([OrderDate])
GO
PRINT 'Creating Index [idxOrdersShippedDate]'
CREATE NONCLUSTERED INDEX [idxOrdersShippedDate] ON [Orders] ([ShippedDate])
GO
PRINT 'Creating Index [idxOrdersShipVia]'
CREATE NONCLUSTERED INDEX [idxOrdersShipVia] ON [Orders] ([ShipVia])
GO
PRINT 'Creating Index [idxOrdersShipPostalCode]'
CREATE NONCLUSTERED INDEX [idxOrdersShipPostalCode] ON [Orders] ([ShipPostalCode])
GO
PRINT 'Creating Index [idxProductsCategoryID]'
CREATE NONCLUSTERED INDEX [idxProductsCategoryID] ON [Products] ([CategoryID])
GO
PRINT 'Creating Index [idxProductsProductName]'
CREATE NONCLUSTERED INDEX [idxProductsProductName] ON [Products] ([ProductName])
GO
PRINT 'Creating Index [idxProductsSupplierID]'
CREATE NONCLUSTERED INDEX [idxProductsSupplierID] ON [Products] ([SupplierID])
GO
PRINT 'Creating Index [idxSuppliersCompanyName]'
CREATE NONCLUSTERED INDEX [idxSuppliersCompanyName] ON [Suppliers] ([CompanyName])
GO
PRINT 'Creating Index [idxSuppliersPostalCode]'
CREATE NONCLUSTERED INDEX [idxSuppliersPostalCode] ON [Suppliers] ([PostalCode])
GO